|
оптимизация limit по union all (когда лимит нельзя перенести в подзапрос)
#39520488
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Москва
Сообщения: 2 843
|
|
Постгрес может эффективно выполнить такой запрос - выбрав не все строки, а только нужные по лимиту. Важно, чтобы на каждой таблице существовал нужный индекс, чтобы не требовалось переупорядочивание строк. Тогда сможет использовать Merge Append, джоин по Nested Loop и Limit.
Вот пример такого боевого запроса:
EXPLAIN (ANALYZE) SELECT usage.req_begin AS usage_req_begin, usage.req_started AS usage_req_started, usage.req_max AS usage_req_max, usage.req_dur AS usage_req_dur, usage.sess_begin AS usage_sess_begin, usage.sess_started AS usage_sess_started, usage.sess_max AS usage_sess_max, usage.sess_dur AS usage_sess_dur, usage.bytes_out AS usage_bytes_out, usage.bytes_out_cached AS usage_bytes_out_cached, usage.max_interval_bytes AS usage_max_interval_bytes, time.year AS time_year, time.month AS time_month, time.day AS time_day, time.hour AS time_hour, time.minute AS time_minute, geo.country AS geo_country, geo.region AS geo_region, useragent.browser AS useragent_browser, useragent.platform AS useragent_platform, backend.torso_id AS backend_torso_id, proto.name AS proto_name, referrer.name AS referrer_name, custom.field1 AS custom_field1, status.code AS status_code, clienturi.client_id AS clienturi_client_id, clienturi.hostname AS clienturi_hostname, clienturi.uri AS clienturi_uri FROM usage INNER JOIN (VALUES (448540, 753, 4176, 210, 67, 11713775, 1, 1, 649608573)) AS ids(time_id, geo_id, useragent_id, backend_id, proto_id, referrer_id, custom_id, status_id, clienturi_parent_id) ON (usage.time_id, usage.geo_id, usage.useragent_id, usage.backend_id, usage.proto_id, usage.referrer_id, usage.custom_id, usage.status_id, usage.clienturi_parent_id) = (ids.time_id, ids.geo_id, ids.useragent_id, ids.backend_id, ids.proto_id, ids.referrer_id, ids.custom_id, ids.status_id, ids.clienturi_parent_id) CROSS JOIN LATERAL (SELECT time.* FROM time WHERE time.id = usage.time_id LIMIT 1) AS time CROSS JOIN LATERAL (SELECT geo.* FROM geo WHERE geo.id = usage.geo_id LIMIT 1) AS geo CROSS JOIN LATERAL (SELECT useragent.* FROM useragent WHERE useragent.id = usage.useragent_id LIMIT 1) AS useragent CROSS JOIN LATERAL (SELECT backend.* FROM backend WHERE backend.id = usage.backend_id LIMIT 1) AS backend CROSS JOIN LATERAL (SELECT proto.* FROM proto WHERE proto.id = usage.proto_id LIMIT 1) AS proto CROSS JOIN LATERAL (SELECT referrer.* FROM referrer WHERE referrer.id = usage.referrer_id LIMIT 1) AS referrer CROSS JOIN LATERAL (SELECT custom.* FROM custom WHERE custom.id = usage.custom_id LIMIT 1) AS custom CROSS JOIN LATERAL (SELECT status.* FROM status WHERE status.id = usage.status_id LIMIT 1) AS status CROSS JOIN LATERAL (SELECT clienturi.* FROM clienturi WHERE clienturi.id = usage.clienturi_id LIMIT 1) AS clienturi ORDER BY usage_bytes_out desc LIMIT 10
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=8.30..489.06 rows=10 width=281) (actual time=0.296..0.890 rows=10 loops=1)
-> Nested Loop (cost=8.30..633.29 rows=13 width=281) (actual time=0.296..0.889 rows=10 loops=1)
-> Nested Loop (cost=8.30..558.59 rows=13 width=215) (actual time=0.273..0.778 rows=10 loops=1)
-> Nested Loop (cost=8.30..515.82 rows=13 width=215) (actual time=0.267..0.744 rows=10 loops=1)
-> Nested Loop (cost=8.30..443.58 rows=13 width=186) (actual time=0.262..0.712 rows=10 loops=1)
-> Nested Loop (cost=8.30..370.06 rows=13 width=165) (actual time=0.244..0.646 rows=10 loops=1)
-> Nested Loop (cost=8.30..358.32 rows=13 width=153) (actual time=0.226..0.563 rows=10 loops=1)
-> Nested Loop (cost=8.30..330.43 rows=13 width=151) (actual time=0.189..0.347 rows=10 loops=1)
-> Nested Loop (cost=8.30..258.27 rows=13 width=114) (actual time=0.178..0.293 rows=10 loops=1)
-> Nested Loop (cost=8.30..186.14 rows=13 width=90) (actual time=0.167..0.240 rows=10 loops=1)
-> Merge Append (cost=8.30..113.30 rows=13 width=74) (actual time=0.146..0.169 rows=10 loops=1)
Sort Key: usage.bytes_out DESC
-> Index Scan Backward using usage_time_id_clienturi_parent_id_geo_id_useragent_id_backe_idx on usage (cost=0.12..8.16 rows=1 width=74) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_time_id_clienturi_parent_id_geo_id_useragent_id__idx on usage_2017 (cost=0.70..8.74 rows=1 width=74) (actual time=0.028..0.049 rows=10 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2016_time_id_clienturi_parent_id_geo_id_useragent_id__idx on usage_2016 (cost=0.70..8.74 rows=1 width=74) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_04_time_id_clienturi_parent_id_geo_id_useragent__idx on usage_2017_04 (cost=0.71..8.75 rows=1 width=74) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_03_time_id_clienturi_parent_id_geo_id_useragent__idx on usage_2017_03 (cost=0.71..8.74 rows=1 width=74) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_06_time_id_clienturi_parent_id_geo_id_useragent__idx on usage_2017_06 (cost=0.71..8.75 rows=1 width=74) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_05_time_id_clienturi_parent_id_geo_id_useragent__idx on usage_2017_05 (cost=0.71..8.75 rows=1 width=74) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_02_time_id_clienturi_parent_id_geo_id_useragent__idx on usage_2017_02 (cost=0.71..8.74 rows=1 width=74) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_01_time_id_clienturi_parent_id_geo_id_useragent__idx on usage_2017_01 (cost=0.71..8.74 rows=1 width=74) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_07_time_id_clienturi_parent_id_geo_id_useragent__idx on usage_2017_07 (cost=0.71..8.75 rows=1 width=74) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_08_time_id_clienturi_parent_id_geo_id_useragent__idx on usage_2017_08 (cost=0.71..8.75 rows=1 width=74) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_09_time_id_clienturi_parent_id_geo_id_useragent__idx on usage_2017_09 (cost=0.71..8.74 rows=1 width
=74) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 21
0) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Index Scan Backward using usage_2017_10_time_id_clienturi_parent_id_geo_id_useragent__idx on usage_2017_10 (cost=0.15..8.19 rows=1 width=74) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((time_id = 448540) AND (clienturi_parent_id = 649608573) AND (geo_id = 753) AND (useragent_id = 4176) AND (backend_id = 210) AND (proto_id = 67) AND (referrer_id = 11713775) AND (status_id = 1) AND (custom_id = 1))
-> Limit (cost=0.00..5.58 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=10)
-> Result (cost=0.00..16.75 rows=3 width=20) (actual time=0.006..0.006 rows=1 loops=10)
-> Append (cost=0.00..16.75 rows=3 width=20) (actual time=0.005..0.005 rows=1 loops=10)
-> Seq Scan on "time" (cost=0.00..0.00 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=10)
Filter: (id = usage.time_id)
-> Index Scan using time_2016_pkey on time_2016 (cost=0.42..8.44 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=10)
Index Cond: (id = usage.time_id)
-> Index Scan using time_2017_pkey on time_2017 (cost=0.29..8.31 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=10)
Index Cond: (id = usage.time_id)
-> Limit (cost=0.00..5.53 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=10)
-> Result (cost=0.00..16.59 rows=3 width=26) (actual time=0.005..0.005 rows=1 loops=10)
-> Append (cost=0.00..16.59 rows=3 width=26) (actual time=0.004..0.004 rows=1 loops=10)
-> Seq Scan on geo (cost=0.00..0.00 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=10)
Filter: (id = usage.geo_id)
-> Index Scan using geo_2016_pkey on geo_2016 (cost=0.28..8.29 rows=1 width=7) (actual time=0.001..0.001 rows=0 loops=10)
Index Cond: (id = usage.geo_id)
-> Index Scan using geo_2017_pkey on geo_2017 (cost=0.28..8.29 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=10)
Index Cond: (id = usage.geo_id)
-> Limit (cost=0.00..5.53 rows=1 width=38) (actual time=0.005..0.005 rows=1 loops=10)
-> Result (cost=0.00..16.59 rows=3 width=38) (actual time=0.004..0.004 rows=1 loops=10)
-> Append (cost=0.00..16.59 rows=3 width=38) (actual time=0.004..0.004 rows=1 loops=10)
-> Seq Scan on useragent (cost=0.00..0.00 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=10)
Filter: (id = usage.useragent_id)
-> Index Scan using useragent_2016_pkey on useragent_2016 (cost=0.28..8.30 rows=1 width=31) (actual time=0.001..0.001 rows=0 loops=10)
Index Cond: (id = usage.useragent_id)
-> Index Scan using useragent_2017_pkey on useragent_2017 (cost=0.28..8.29 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=10)
Index Cond: (id = usage.useragent_id)
-> Limit (cost=0.00..2.12 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=10)
-> Result (cost=0.00..6.38 rows=3 width=4) (actual time=0.021..0.021 rows=1 loops=10)
-> Append (cost=0.00..6.38 rows=3 width=4) (actual time=0.021..0.021 rows=1 loops=10)
-> Seq Scan on backend (cost=0.00..0.00 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=10)
Filter: (id = usage.backend_id)
-> Seq Scan on backend_2016 (cost=0.00..3.43 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=10)
Filter: (id = usage.backend_id)
Rows Removed by Filter: 194
-> Seq Scan on backend_2017 (cost=0.00..2.95 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=10)
Filter: (id = usage.backend_id)
-> Limit (cost=0.00..0.88 rows=1 width=14) (actual time=0.008..0.008 rows=1 loops=10)
-> Result (cost=0.00..2.65 rows=3 width=14) (actual time=0.007..0.007 rows=1 loops=10)
-> Append (cost=0.00..2.65 rows=3 width=14) (actual time=0.007..0.007 rows=1 loops=10)
-> Seq Scan on proto (cost=0.00..0.00 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=10)
Filter: (id = usage.proto_id)
-> Seq Scan on proto_2016 (cost=0.00..1.61 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=10)
Filter: (id = usage.proto_id)
Rows Removed by Filter: 49
-> Seq Scan on proto_2017 (cost=0.00..1.04 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=10)
Filter: (id = usage.proto_id)
-> Limit (cost=0.00..5.64 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=10)
-> Result (cost=0.00..16.91 rows=3 width=25) (actual time=0.006..0.006 rows=1 loops=10)
-> Append (cost=0.00..16.91 rows=3 width=25) (actual time=0.006..0.006 rows=1 loops=10)
-> Seq Scan on referrer (cost=0.00..0.00 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=10)
Filter: (id = usage.referrer_id)
-> Index Scan using referrer_2016_pkey on referrer_2016 (cost=0.43..8.45 rows=1 width=22) (actual time=0.002..0.002 rows=0 loops=10)
Index Cond: (id = usage.referrer_id)
-> Index Scan using referrer_2017_pkey on referrer_2017 (cost=0.43..8.45 rows=1 width=22) (actual time=0.003..0.003 rows=1 loops=10)
Index Cond: (id = usage.referrer_id)
-> Limit (cost=0.00..5.54 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=10)
-> Result (cost=0.00..16.61 rows=3 width=33) (actual time=0.002..0.002 rows=1 loops=10)
-> Append (cost=0.00..16.61 rows=3 width=33) (actual time=0.002..0.002 rows=1 loops=10)
-> Seq Scan on custom (cost=0.00..0.00 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=10)
Filter: (id = usage.custom_id)
-> Index Scan using custom_2016_pkey on custom_2016 (cost=0.15..8.17 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=10)
Index Cond: (id = usage.custom_id)
-> Index Scan using custom_2017_pkey on custom_2017 (cost=0.42..8.44 rows=1 width=36) (never executed)
Index Cond: (id = usage.custom_id)
-> Limit (cost=0.00..3.27 rows=1 width=2) (actual time=0.003..0.003 rows=1 loops=10)
-> Result (cost=0.00..9.81 rows=3 width=2) (actual time=0.003..0.003 rows=1 loops=10)
-> Append (cost=0.00..9.81 rows=3 width=2) (actual time=0.002..0.002 rows=1 loops=10)
-> Seq Scan on status (cost=0.00..0.00 rows=1 width=2) (actual time=0.000..0.000 rows=0 loops=10)
Filter: (id = usage.status_id)
-> Index Scan using status_2016_pkey on status_2016 (cost=0.15..8.17 rows=1 width=2) (actual time=0.001..0.001 rows=1 loops=10)
Index Cond: (id = usage.status_id)
-> Seq Scan on status_2017 (cost=0.00..1.64 rows=1 width=2) (never executed)
Filter: (id = usage.status_id)
-> Limit (cost=0.00..5.73 rows=1 width=73) (actual time=0.010..0.010 rows=1 loops=10)
-> Result (cost=0.00..17.18 rows=3 width=73) (actual time=0.010..0.010 rows=1 loops=10)
-> Append (cost=0.00..17.18 rows=3 width=73) (actual time=0.010..0.010 rows=1 loops=10)
-> Seq Scan on clienturi (cost=0.00..0.00 rows=1 width=68) (actual time=0.000..0.000 rows=0 loops=10)
Filter: (id = usage.clienturi_id)
-> Index Scan using clienturi_2016_pkey1 on clienturi_2016 (cost=0.57..8.59 rows=1 width=73) (actual time=0.002..0.002 rows=0 loops=10)
Index Cond: (id = usage.clienturi_id)
-> Index Scan using clienturi_2017_pkey on clienturi_2017 (cost=0.57..8.59 rows=1 width=79) (actual time=0.006..0.006 rows=1 loops=10)
Index Cond: (id = usage.clienturi_id)
Planning time: 4.237 ms
Execution time: 1.592 ms
(123 строки)
|
|
|
