· 8 years ago · Aug 15, 2018, 12:08 AM
1Very slow PostgreSQL query
2EXPLAIN
3SELECT "report_rank"."id", "report_rank"."keyword_id", "report_rank"."site_id"
4 , "report_rank"."rank", "report_rank"."url", "report_rank"."competition"
5 , "report_rank"."source", "report_rank"."country", "report_rank"."created"
6 , MAX(T7."created") AS "max"
7FROM "report_rank"
8LEFT OUTER JOIN "report_site"
9 ON ("report_rank"."site_id" = "report_site"."id")
10INNER JOIN "report_profile"
11 ON ("report_site"."id" = "report_profile"."site_id")
12INNER JOIN "crm_client"
13 ON ("report_profile"."client_id" = "crm_client"."id")
14INNER JOIN "auth_user"
15 ON ("crm_client"."user_id" = "auth_user"."id")
16LEFT OUTER JOIN "report_rank" T7
17 ON ("report_site"."id" = T7."site_id")
18WHERE ("auth_user"."is_active" = True AND "crm_client"."is_deleted" = False )
19GROUP BY "report_rank"."id", "report_rank"."keyword_id", "report_rank"."site_id"
20 , "report_rank"."rank", "report_rank"."url", "report_rank"."competition"
21 , "report_rank"."source", "report_rank"."country", "report_rank"."created"
22HAVING MAX(T7."created") = "report_rank"."created";
23
24GroupAggregate (cost=1136244292.46..1276589375.47 rows=48133327 width=72)
25 Filter: (max(t7.created) = report_rank.created)
26 -> Sort (cost=1136244292.46..1147889577.16 rows=4658113881 width=72)
27 Sort Key: report_rank.id, report_rank.keyword_id, report_rank.site_id, report_rank.rank, report_rank.url, report_rank.competition, report_rank.source, report_rank.country, report_rank.created
28 -> Hash Join (cost=1323766.36..6107863.59 rows=4658113881 width=72)
29 Hash Cond: (report_rank.site_id = report_site.id)
30 -> Seq Scan on report_rank (cost=0.00..1076119.27 rows=48133327 width=64)
31 -> Hash (cost=1312601.51..1312601.51 rows=893188 width=16)
32 -> Hash Right Join (cost=47050.38..1312601.51 rows=893188 width=16)
33 Hash Cond: (t7.site_id = report_site.id)
34 -> Seq Scan on report_rank t7 (cost=0.00..1076119.27 rows=48133327 width=12)
35 -> Hash (cost=46692.28..46692.28 rows=28648 width=8)
36 -> Nested Loop (cost=2201.98..46692.28 rows=28648 width=8)
37 -> Hash Join (cost=2201.98..5733.23 rows=28648 width=4)
38 Hash Cond: (crm_client.user_id = auth_user.id)
39 -> Hash Join (cost=2040.73..5006.71 rows=44606 width=8)
40 Hash Cond: (report_profile.client_id = crm_client.id)
41 -> Seq Scan on report_profile (cost=0.00..1706.09 rows=93009 width=8)
42 -> Hash (cost=1761.98..1761.98 rows=22300 width=8)
43 -> Seq Scan on crm_client (cost=0.00..1761.98 rows=22300 width=8)
44 Filter: (NOT is_deleted)
45 -> Hash (cost=126.85..126.85 rows=2752 width=4)
46 -> Seq Scan on auth_user (cost=0.00..126.85 rows=2752 width=4)
47 Filter: is_active
48 -> Index Scan using report_site_pkey on report_site (cost=0.00..1.42 rows=1 width=4)
49 Index Cond: (id = report_profile.site_id)
50
51WITH x AS (
52 SELECT max(r0.created) AS max_created
53 FROM report_rank r0
54 WHERE EXISTS (
55 SELECT *
56 FROM report_site s0 ON s0.id = r0.site_id
57 JOIN report_profile p0 ON p0.site_id = s0.id
58 JOIN crm_client c0 ON c0.id = p0.client_id
59 JOIN auth_user u0 ON u0.id = c0.user_id
60 WHERE s0.id = r0.site_id
61 AND u0.is_active
62 AND c0.is_deleted = FALSE)
63 )
64SELECT r.id
65 ,r.keyword_id
66 ,r.site_id
67 ,r.rank
68 ,r.url
69 ,r.competition
70 ,r.source
71 ,r.country
72 ,x.max_created -- identical to r.created
73FROM x
74JOIN report_rank r ON r.created = x.max_created
75WHERE EXISTS (
76 SELECT *
77 FROM report_site s ON s.id = r.site_id
78 JOIN report_profile p ON p.site_id = s.id
79 JOIN crm_client c ON c.id = p.client_id
80 JOIN auth_user u ON u.id = c.user_id
81 WHERE s.id = r.site_id
82 AND u.is_active
83 AND c.is_deleted = FALSE);
84
85SELECT r.id
86 ,r.keyword_id
87 ,r.site_id
88 ,r.rank
89 ,r.url
90 ,r.competition
91 ,r.source
92 ,r.country
93 ,r.created AS max_created
94FROM report_rank r
95WHERE -- r.created > f_report_rank_cap() AND
96 EXISTS (
97 SELECT 1
98 FROM report_site s ON s.id = r.site_id
99 JOIN report_profile p ON p.site_id = s.id
100 JOIN crm_client c ON c.id = p.client_id
101 JOIN auth_user u ON u.id = c.user_id
102 WHERE s.id = r.site_id
103 AND u.is_active
104 AND c.is_deleted = FALSE)
105ORDER BY r.created DESC
106LIMIT 1;
107
108r.created > f_report_rank_cap()
109
110-- DROP SCHEMA x CASCADE;
111CREATE SCHEMA x;
112
113CREATE TABLE x.report_rank(created timestamp);
114INSERT INTO x.report_rank VALUES ('2011-11-11 11:11'),(now());
115
116-- create function initially
117CREATE OR REPLACE FUNCTION x.f_report_rank_cap()
118 RETURNS timestamp AS
119$y$
120BEGIN
121
122RETURN '1970-1-1 0:0'::timestamp; -- Start low, timestamp will be updated
123
124END;
125$y$
126 LANGUAGE plpgsql COST 1 IMMUTABLE;
127
128-- function to update partial index & function
129CREATE OR REPLACE FUNCTION x.f_report_rank_set_cap()
130 RETURNS void AS
131$BODY$
132DECLARE
133 _secure_margin CONSTANT interval := interval '1d'; -- adjust to your needs
134 _cap timestamp; -- cap older rows than this
135BEGIN
136
137SELECT max(created) - _secure_margin
138FROM x.report_rank
139WHERE created >= x.f_report_rank_cap()
140/* not needed for the demo; @erikcw needs to activate this
141AND EXISTS (
142 SELECT *
143 FROM report_site s
144 JOIN report_profile p ON p.site_id = s.id
145 JOIN crm_client c ON c.id = p.client_id
146 JOIN auth_user u ON u.id = c.user_id
147 WHERE s.id = r.site_id
148 AND u.is_active
149 AND c.is_deleted = FALSE)
150*/
151INTO _cap;
152
153IF FOUND THEN
154 -- recreate function -- you have to create it manually once!
155 EXECUTE '
156 CREATE OR REPLACE FUNCTION x.f_report_rank_cap()
157 RETURNS timestamp AS
158 $y$
159 BEGIN
160
161 RETURN '''|| _cap ||'''::timestamp;
162
163 END;
164 $y$
165 LANGUAGE plpgsql IMMUTABLE';
166
167 -- drop index
168 EXECUTE 'DROP INDEX IF EXISTS x.report_rank_recent_idx;';
169
170 -- create new one
171 EXECUTE '
172 CREATE INDEX report_rank_recent_idx
173 ON x.report_rank (created)
174 WHERE created > ''' || _cap ||'''';
175END IF;
176
177END;
178$BODY$
179 LANGUAGE plpgsql VOLATILE;
180
181COMMENT ON FUNCTION x.f_report_rank_set_cap() IS 'Dynamically generate partial index on report_rank adn function f_report_rank_cap().';
182
183SELECT x.f_report_rank_set_cap();
184
185SELECT x.f_report_rank_cap();
186
187-- modelled after Erwin's version
188-- does the x query really return only one row?
189
190SELECT r.id, r.keyword_id, r.site_id
191 , r.rank, r.url, r.competition, r.source
192 , r.country, r.created, x.max_created
193-- UPDATE3: I forgot one, too
194FROM report_rank r
195LEFT JOIN report_site s ON (r.site_id = s.id)
196JOIN report_profile p ON (s.id = p.site_id)
197JOIN crm_client c ON (p.client_id = c.id)
198JOIN auth_user u ON (c.user_id = u.id)
199-- UPDATE2: t7 has left the building
200WHERE u.is_active
201AND c.is_deleted = FALSE
202AND NOT EXISTS (SELECT * FROM report_rank x
203 -- WHERE 1=1 -- uncorrelated subquery ??
204 -- UPDATE1: no it's not. Erwin seems to have forgotten the t7 join
205 WHERE r.id = x.site_id
206 AND x.created > r.created
207 )
208;
209
210SELECT r.id, r.keyword_id, r.site_id, r.rank, r.url, r.competition
211 ,r.source, r.country, r.created
212 ,MAX(t7.created) AS max
213FROM report_rank r
214LEFT JOIN report_site s ON (s.id = r.site_id)
215JOIN report_profile p ON (p.site_id = s.id)
216JOIN crm_client c ON (c.id = p.client_id)
217JOIN auth_user u ON (u.id = c.user_id)
218LEFT JOIN report_rank t7 ON (t.site_id = s.id)
219WHERE u.is_active
220AND c.is_deleted = False
221GROUP BY
222 r.id
223 ,r.keyword_id
224 ,r.site_id
225 ,r.rank
226 ,r.url, r.competition
227 ,r.source
228 ,r.country
229 ,r.created
230HAVING MAX(t7.created) = r.created;
231
232SELECT r.*
233FROM report_rank r
234JOIN report_profile p USING (site_id)
235JOIN crm_client c ON (c.id = p.client_id)
236JOIN auth_user u ON (u.id = c.user_id)
237WHERE u.is_active
238AND c.is_deleted = FALSE
239GROUP BY r.id;
240
241WITH p AS (
242 SELECT p.id AS profile_id
243 ,p.site_id
244 FROM report_profile p
245 WHERE EXISTS (
246 SELECT *
247 FROM crm_client c
248 JOIN auth_user u ON u.id = c.user_id
249 WHERE c.id = p.client_id
250 AND c.is_deleted = FALSE
251 AND u.is_active
252 )
253 ) x AS (
254 SELECT p.profile_id
255 ,r.*
256 FROM p
257 JOIN report_rank r USING (site_id)
258 )
259SELECT *
260FROM x
261WHERE NOT EXISTS (
262 SELECT *
263 FROM x r
264 WHERE r.profile_id = x.profile_id
265 AND r.created > x.created
266 );