Гость
Форумы
[новые:0]
/ PostgreSQL
[новые:0]
[игнор отключен]
[закрыт для гостей]
/
Джойн на партицированную таблицу
[новые:0]
10 сообщений из 10, страница 1 из 1
|
|
|
Джойн на партицированную таблицу
|
|||
|---|---|---|---|
|
#18+
Всем привет. С postgre работаю не так давно, поэтому не судите строго. Есть партицированная по placed_time табличка bet_parts (реализовано через inheritance). Parent-таблица пустая, все данные в дочерних таблицах. На каждой из дочерних таблиц создан индекс (на родительской нет, как и рекомендует мануал): Код: plsql 1. 2. 3. 4. Выполняю следующий запрос. Ожидаю увидеть NL + в поиск в индексе в каждой партиции: Код: plsql 1. 2. 3. 4. Получаю план: Merge"Aggregate (cost=59885720.28..59885720.29 rows=1 width=0)" " -> Merge Left Join (cost=40984773.50..59110577.90 rows=310056952 width=0)" " Merge Cond: (b.id = p.bet_id)" " -> Index Only Scan using bets_history_id_oca_modified_idx on bets_history b (cost=0.56..435569.84 rows=1148635 width=8)" " Index Cond: ((modified >= '2018-04-07 00:00:00+00'::timestamp with time zone) AND (modified < '2018-04-08 00:00:00+00'::timestamp with time zone))" " -> Materialize (cost=20.48..54027873.53 rows=617477371 width=8)" " -> Merge Append (cost=20.48..52484180.10 rows=617477371 width=8)" " Sort Key: p.bet_id" " -> Sort (cost=0.01..0.02 rows=1 width=8)" " Sort Key: p.bet_id" " -> Seq Scan on bet_parts p (cost=0.00..0.00 rows=1 width=8)" " -> Index Only Scan using bet_parts_201503_bet_id_idx on bet_parts_201503 p_1 (cost=0.29..1094.89 rows=42307 width=8)" " -> Index Only Scan using bet_parts_201504_bet_id_idx on bet_parts_201504 p_2 (cost=0.42..4618.39 rows=164931 width=8)" " -> Index Only Scan using bet_parts_201505_bet_id_idx on bet_parts_201505 p_3 (cost=0.42..15489.38 rows=580597 width=8)" " -> Index Only Scan using bet_parts_201506_bet_id_idx on bet_parts_201506 p_4 (cost=0.42..25528.89 rows=978031 width=8)" " -> Index Only Scan using bet_parts_201507_bet_id_idx on bet_parts_201507 p_5 (cost=0.43..35566.69 rows=1339751 width=8)" " -> Index Only Scan using bet_parts_201508_bet_id_idx on bet_parts_201508 p_6 (cost=0.43..104907.93 rows=3930100 width=8)" " -> Index Only Scan using bet_parts_201509_bet_id_idx on bet_parts_201509 p_7 (cost=0.43..165336.18 rows=6567983 width=8)" " -> Index Only Scan using bet_parts_201510_bet_id_idx on bet_parts_201510 p_8 (cost=0.43..191525.78 rows=7800623 width=8)" Скан bet_history - это ок, в таблице хранятся только последние несколько дней. Непонятно почему сервер ожидает 300m строк после джойна. Код: plsql 1. NestedLoops"Aggregate (cost=123188147.79..123188147.80 rows=1 width=0)" " -> Nested Loop Left Join (cost=0.00..122412979.26 rows=310067414 width=0)" " -> Seq Scan on bets_history b (cost=0.00..412036.61 rows=1148635 width=8)" " Filter: ((modified >= '2018-04-07 00:00:00+00'::timestamp with time zone) AND (modified < '2018-04-08 00:00:00+00'::timestamp with time zone))" " -> Append (cost=0.00..104.62 rows=159 width=8)" " -> Seq Scan on bet_parts p (cost=0.00..0.00 rows=1 width=8)" " Filter: (bet_id = b.id)" " -> Index Only Scan using bet_parts_201503_bet_id_idx on bet_parts_201503 p_1 (cost=0.29..2.08 rows=2 width=8)" " Index Cond: (bet_id = b.id)" " -> Index Only Scan using bet_parts_201504_bet_id_result_type_id_placed_time_idx1 on bet_parts_201504 p_2 (cost=0.42..2.23 rows=3 width=8)" " Index Cond: (bet_id = b.id)" " -> Index Only Scan using bet_parts_201505_bet_id_idx on bet_parts_201505 p_3 (cost=0.42..2.24 rows=3 width=8)" " Index Cond: (bet_id = b.id)" " -> Index Only Scan using bet_parts_201506_bet_id_idx on bet_parts_201506 p_4 (cost=0.42..2.22 rows=2 width=8)" " Index Cond: (bet_id = b.id)" " -> Index Only Scan using bet_parts_201507_bet_id_idx on bet_parts_201507 p_5 (cost=0.43..2.24 rows=3 width=8)" " Index Cond: (bet_id = b.id)" " -> Index Only Scan using bet_parts_201508_bet_id_idx on bet_parts_201508 p_6 (cost=0.43..2.24 rows=3 width=8)" " Index Cond: (bet_id = b.id)" " -> Index Only Scan using bet_parts_201509_bet_id_idx on bet_parts_201509 p_7 (cost=0.43..2.25 rows=3 width=8)" " Index Cond: (bet_id = b.id)" " -> Index Only Scan using bet_parts_201510_bet_id_idx on bet_parts_201510 p_8 (cost=0.43..2.27 rows=4 width=8)" Я так понимаю проблема в том, что сервер не видит общую статистику по bet_parts и всем ее партициям. Поидее он должен взять общий n_distinct и узнать что после Append'a партиций должно быть так же 4 строки, а не сумма из каждой партиции. Смотрю: Код: plsql 1. 2. 3. И вижу что данные для parent таблицы там какие-то левые. Запустил вручную и поставил актуальное значение: Код: plsql 1. 2. Но это не помогло, при этом в pg_stats вижу все так же старое левое число. Куда копать? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.04.2018, 13:54 |
|
||
|
Джойн на партицированную таблицу
|
|||
|---|---|---|---|
|
#18+
Забыл добавить, NL выполняется быстро, Merge - очень долго. Хочу чтобы был NL без использования хинтов. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.04.2018, 13:56 |
|
||
|
Джойн на партицированную таблицу
|
|||
|---|---|---|---|
|
#18+
Remind, explain analyze запросов покажите. после альтеров с n_distinct надо делать analyze насколько я помню. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.04.2018, 14:50 |
|
||
|
Джойн на партицированную таблицу
|
|||
|---|---|---|---|
|
#18+
Alexius, NestedLoop"Aggregate (cost=122682854.99..122682855.00 rows=1 width=0) (actual time=144725.051..144725.052 rows=1 loops=1)" " -> Nested Loop Left Join (cost=0.00..121907682.33 rows=310069065 width=0) (actual time=1.875..144244.732 rows=4029436 loops=1)" " -> Seq Scan on bets_history b (cost=0.00..412036.61 rows=1148635 width=8) (actual time=0.085..7590.979 rows=927931 loops=1)" " Filter: ((modified >= '2018-04-07 00:00:00+00'::timestamp with time zone) AND (modified < '2018-04-08 00:00:00+00'::timestamp with time zone))" " Rows Removed by Filter: 3173571" " -> Append (cost=0.00..104.18 rows=159 width=8) (actual time=0.131..0.144 rows=4 loops=927931)" " -> Seq Scan on bet_parts p (cost=0.00..0.00 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=927931)" " Filter: (bet_id = b.id)" " -> Index Only Scan using bet_parts_201503_bet_id_idx on bet_parts_201503 p_1 (cost=0.29..2.08 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201504_bet_id_result_type_id_placed_time_idx1 on bet_parts_201504 p_2 (cost=0.42..2.23 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201505_bet_id_idx on bet_parts_201505 p_3 (cost=0.42..2.24 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201506_bet_id_idx on bet_parts_201506 p_4 (cost=0.42..2.22 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201507_bet_id_idx on bet_parts_201507 p_5 (cost=0.43..2.24 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201508_bet_id_idx on bet_parts_201508 p_6 (cost=0.43..2.24 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201509_bet_id_idx on bet_parts_201509 p_7 (cost=0.43..2.25 rows=3 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201510_bet_id_idx on bet_parts_201510 p_8 (cost=0.43..2.27 rows=4 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201511_bet_id_idx on bet_parts_201511 p_9 (cost=0.43..2.27 rows=4 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201512_bet_id_idx on bet_parts_201512 p_10 (cost=0.43..2.27 rows=4 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_default_bet_id_idx on bet_parts_default p_11 (cost=0.42..2.77 rows=5 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 86" " -> Index Only Scan using bet_parts_201601_bet_id_idx on bet_parts_201601 p_12 (cost=0.43..2.27 rows=4 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201602_bet_id_idx on bet_parts_201602 p_13 (cost=0.43..2.27 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201603_bet_id_idx on bet_parts_201603 p_14 (cost=0.43..2.25 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201604_bet_id_idx on bet_parts_201604 p_15 (cost=0.43..2.25 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201605_bet_id_idx on bet_parts_201605 p_16 (cost=0.43..2.25 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201606_bet_id_idx on bet_parts_201606 p_17 (cost=0.43..2.25 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201607_bet_id_idx on bet_parts_201607 p_18 (cost=0.43..2.25 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201608_bet_id_idx on bet_parts_201608 p_19 (cost=0.43..2.27 rows=4 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201609_bet_id_idx on bet_parts_201609 p_20 (cost=0.44..2.27 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201610_bet_id_idx on bet_parts_201610 p_21 (cost=0.44..2.25 rows=3 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201611_bet_id_idx on bet_parts_201611 p_22 (cost=0.44..2.27 rows=4 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201612_bet_id_idx on bet_parts_201612 p_23 (cost=0.44..2.27 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201701_bet_id_idx on bet_parts_201701 p_24 (cost=0.44..2.27 rows=4 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201702_bet_id_idx on bet_parts_201702 p_25 (cost=0.44..2.27 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201703_bet_id_idx on bet_parts_201703 p_26 (cost=0.56..2.40 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201704_bet_id_idx on bet_parts_201704 p_27 (cost=0.56..2.40 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201705_bet_id_idx on bet_parts_201705 p_28 (cost=0.44..2.27 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201706_bet_id_idx on bet_parts_201706 p_29 (cost=0.43..2.27 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201707_bet_id_idx on bet_parts_201707 p_30 (cost=0.43..2.27 rows=4 width=8) (actual time=0.002..0.002 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201708_bet_id_idx on bet_parts_201708 p_31 (cost=0.56..2.40 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201709_bet_id_idx on bet_parts_201709 p_32 (cost=0.56..2.40 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201710_bet_id_idx on bet_parts_201710 p_33 (cost=0.56..2.40 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201711_bet_id_idx on bet_parts_201711 p_34 (cost=0.56..2.40 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201712_bet_id_idx on bet_parts_201712 p_35 (cost=0.56..2.41 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201801_bet_id_idx on bet_parts_201801 p_36 (cost=0.56..2.40 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201802_bet_id_idx on bet_parts_201802 p_37 (cost=0.56..2.40 rows=4 width=8) (actual time=0.003..0.003 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201803_bet_id_idx on bet_parts_201803 p_38 (cost=0.56..2.40 rows=4 width=8) (actual time=0.004..0.004 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 150" " -> Index Only Scan using bet_parts_201804_bet_id_idx on bet_parts_201804 p_39 (cost=0.43..2.61 rows=12 width=8) (actual time=0.020..0.023 rows=4 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 160395" " -> Index Only Scan using bet_parts_201805_bet_id_idx on bet_parts_201805 p_40 (cost=0.14..1.75 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201806_bet_id_idx on bet_parts_201806 p_41 (cost=0.14..1.75 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201807_bet_id_idx on bet_parts_201807 p_42 (cost=0.14..1.75 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201808_bet_id_idx on bet_parts_201808 p_43 (cost=0.14..1.75 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201809_bet_id_idx on bet_parts_201809 p_44 (cost=0.14..1.75 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201810_bet_id_idx on bet_parts_201810 p_45 (cost=0.14..1.75 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201811_bet_id_idx on bet_parts_201811 p_46 (cost=0.14..1.75 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201812_bet_id_idx on bet_parts_201812 p_47 (cost=0.14..1.75 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=927931)" " Index Cond: (bet_id = b.id)" " Heap Fetches: 0" "Total runtime: 144726.011 ms" Merge"Aggregate (cost=59341286.70..59341286.71 rows=1 width=0) (actual time=763688.787..763688.787 rows=1 loops=1)" " -> Merge Left Join (cost=28491204.24..58556137.57 rows=314059651 width=0) (actual time=353384.914..763255.068 rows=4029436 loops=1)" " Merge Cond: (b.id = p.bet_id)" " -> Index Only Scan using bets_history_id_oca_modified_idx on bets_history b (cost=0.56..184724.50 rows=1222789 width=8) (actual time=4.668..753.241 rows=927931 loops=1)" " Index Cond: ((modified >= '2018-04-07 00:00:00+00'::timestamp with time zone) AND (modified < '2018-04-08 00:00:00+00'::timestamp with time zone))" " Heap Fetches: 5070" " -> Materialize (cost=20.48..53683996.91 rows=617505072 width=8) (actual time=439.940..675966.257 rows=616194688 loops=1)" " -> Merge Append (cost=20.48..52140234.23 rows=617505072 width=8) (actual time=439.922..553034.436 rows=614680598 loops=1)" " Sort Key: p.bet_id" " -> Sort (cost=0.01..0.02 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=1)" " Sort Key: p.bet_id" " Sort Method: quicksort Memory: 25kB" " -> Seq Scan on bet_parts p (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)" " -> Index Only Scan using bet_parts_201503_bet_id_idx on bet_parts_201503 p_1 (cost=0.29..1094.89 rows=42307 width=8) (actual time=6.319..13.465 rows=42307 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201504_bet_id_idx on bet_parts_201504 p_2 (cost=0.42..4618.39 rows=164931 width=8) (actual time=8.535..40.457 rows=164931 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201505_bet_id_idx on bet_parts_201505 p_3 (cost=0.42..15489.38 rows=580597 width=8) (actual time=12.463..307.382 rows=580433 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201506_bet_id_idx on bet_parts_201506 p_4 (cost=0.42..25528.89 rows=978031 width=8) (actual time=8.405..331.940 rows=978280 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201507_bet_id_idx on bet_parts_201507 p_5 (cost=0.43..35566.69 rows=1339751 width=8) (actual time=11.714..436.997 rows=1339884 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201508_bet_id_idx on bet_parts_201508 p_6 (cost=0.43..104907.93 rows=3930100 width=8) (actual time=10.074..712.016 rows=3930500 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201509_bet_id_idx on bet_parts_201509 p_7 (cost=0.43..165336.18 rows=6567983 width=8) (actual time=10.698..4657.628 rows=6568398 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201510_bet_id_idx on bet_parts_201510 p_8 (cost=0.43..191525.78 rows=7800623 width=8) (actual time=19.679..5248.610 rows=7800902 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201511_bet_id_idx on bet_parts_201511 p_9 (cost=0.43..220063.36 rows=8596328 width=8) (actual time=12.278..2094.966 rows=8596249 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201512_bet_id_idx on bet_parts_201512 p_10 (cost=0.43..225794.69 rows=8826284 width=8) (actual time=15.303..3042.571 rows=8825983 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_default_bet_id_idx on bet_parts_default p_11 (cost=0.42..61889.54 rows=560344 width=8) (actual time=8.320..1536.295 rows=562185 loops=1)" " Heap Fetches: 50250" " -> Index Only Scan using bet_parts_201601_bet_id_idx on bet_parts_201601 p_12 (cost=0.43..248636.75 rows=9710021 width=8) (actual time=15.376..8651.987 rows=9710162 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201602_bet_id_idx on bet_parts_201602 p_13 (cost=0.43..278559.60 rows=11071544 width=8) (actual time=15.119..7254.029 rows=11071382 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201603_bet_id_idx on bet_parts_201603 p_14 (cost=0.43..334659.70 rows=13185684 width=8) (actual time=11.469..4621.237 rows=13185782 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201604_bet_id_idx on bet_parts_201604 p_15 (cost=0.43..338541.21 rows=13301385 width=8) (actual time=13.748..8050.979 rows=13302110 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201605_bet_id_idx on bet_parts_201605 p_16 (cost=0.43..262962.19 rows=10244917 width=8) (actual time=10.328..2608.222 rows=10245538 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201606_bet_id_idx on bet_parts_201606 p_17 (cost=0.43..205687.27 rows=10041122 width=8) (actual time=17.542..1079.391 rows=10041274 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201607_bet_id_idx on bet_parts_201607 p_18 (cost=0.43..237824.41 rows=9166665 width=8) (actual time=16.250..2164.705 rows=9167157 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201608_bet_id_idx on bet_parts_201608 p_19 (cost=0.43..338450.10 rows=13232244 width=8) (actual time=8.525..10632.194 rows=13232058 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201609_bet_id_idx on bet_parts_201609 p_20 (cost=0.44..439303.32 rows=17289792 width=8) (actual time=10.168..9241.693 rows=17289247 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201610_bet_id_idx on bet_parts_201610 p_21 (cost=0.44..518322.68 rows=20527616 width=8) (actual time=14.521..10450.839 rows=20527625 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201611_bet_id_idx on bet_parts_201611 p_22 (cost=0.44..524113.46 rows=20773268 width=8) (actual time=11.902..8610.962 rows=20772502 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201612_bet_id_idx on bet_parts_201612 p_23 (cost=0.44..583974.04 rows=23173840 width=8) (actual time=16.622..9676.683 rows=23174003 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201701_bet_id_idx on bet_parts_201701 p_24 (cost=0.44..486160.28 rows=19273856 width=8) (actual time=11.622..8521.890 rows=19275251 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201702_bet_id_idx on bet_parts_201702 p_25 (cost=0.44..569262.28 rows=22696256 width=8) (actual time=15.738..9484.221 rows=22696552 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201703_bet_id_idx on bet_parts_201703 p_26 (cost=0.56..658687.60 rows=25432574 width=8) (actual time=20.826..9911.490 rows=25432631 loops=1)" " Heap Fetches: 1204" " -> Index Only Scan using bet_parts_201704_bet_id_idx on bet_parts_201704 p_27 (cost=0.56..688547.87 rows=26638784 width=8) (actual time=17.731..17123.090 rows=26638592 loops=1)" " Heap Fetches: 918" " -> Index Only Scan using bet_parts_201705_bet_id_idx on bet_parts_201705 p_28 (cost=0.44..546367.27 rows=20699120 width=8) (actual time=17.830..17808.915 rows=20699157 loops=1)" " Heap Fetches: 1261" " -> Index Only Scan using bet_parts_201706_bet_id_idx on bet_parts_201706 p_29 (cost=0.43..370543.87 rows=13708458 width=8) (actual time=6.752..7168.216 rows=13708779 loops=1)" " Heap Fetches: 3442" " -> Index Only Scan using bet_parts_201707_bet_id_idx on bet_parts_201707 p_30 (cost=0.43..399866.76 rows=14998316 width=8) (actual time=2.568..13100.029 rows=14998371 loops=1)" " Heap Fetches: 1941" " -> Index Only Scan using bet_parts_201708_bet_id_idx on bet_parts_201708 p_31 (cost=0.56..624740.82 rows=23905120 width=8) (actual time=2.973..25800.320 rows=23904913 loops=1)" " Heap Fetches: 2486" " -> Index Only Scan using bet_parts_201709_bet_id_idx on bet_parts_201709 p_32 (cost=0.56..811069.67 rows=31514970 width=8) (actual time=4.548..22055.484 rows=31514889 loops=1)" " Heap Fetches: 9276" " -> Index Only Scan using bet_parts_201710_bet_id_idx on bet_parts_201710 p_33 (cost=0.56..844439.91 rows=32380948 width=8) (actual time=2.788..36489.203 rows=32380884 loops=1)" " Heap Fetches: 39819" " -> Index Only Scan using bet_parts_201711_bet_id_idx on bet_parts_201711 p_34 (cost=0.56..798911.57 rows=30284964 width=8) (actual time=15.587..28566.307 rows=30284995 loops=1)" " Heap Fetches: 5840" " -> Index Only Scan using bet_parts_201712_bet_id_idx on bet_parts_201712 p_35 (cost=0.56..874669.51 rows=32697240 width=8) (actual time=7.478..52894.132 rows=32697200 loops=1)" " Heap Fetches: 143845" " -> Index Only Scan using bet_parts_201801_bet_id_idx on bet_parts_201801 p_36 (cost=0.56..778950.36 rows=29410574 width=8) (actual time=9.646..27623.884 rows=29410509 loops=1)" " Heap Fetches: 13015" " -> Index Only Scan using bet_parts_201802_bet_id_idx on bet_parts_201802 p_37 (cost=0.56..840354.71 rows=32912410 width=8) (actual time=6.297..26903.923 rows=32912410 loops=1)" " Heap Fetches: 708" " -> Index Only Scan using bet_parts_201803_bet_id_idx on bet_parts_201803 p_38 (cost=0.56..962081.55 rows=37064760 width=8) (actual time=6.854..32689.594 rows=38352523 loops=1)" " Heap Fetches: 9771" " -> Index Only Scan using bet_parts_201804_bet_id_idx on bet_parts_201804 p_39 (cost=0.43..491376.77 rows=12781264 width=8) (actual time=5.205..5601.060 rows=8664050 loops=1)" " Heap Fetches: 178230" " -> Index Only Scan using bet_parts_201805_bet_id_idx on bet_parts_201805 p_40 (cost=0.14..20.29 rows=10 width=8) (actual time=0.008..0.008 rows=0 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201806_bet_id_idx on bet_parts_201806 p_41 (cost=0.14..20.29 rows=10 width=8) (actual time=0.005..0.005 rows=0 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201807_bet_id_idx on bet_parts_201807 p_42 (cost=0.14..20.29 rows=10 width=8) (actual time=0.005..0.005 rows=0 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201808_bet_id_idx on bet_parts_201808 p_43 (cost=0.14..20.29 rows=10 width=8) (actual time=0.004..0.004 rows=0 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201809_bet_id_idx on bet_parts_201809 p_44 (cost=0.14..20.29 rows=10 width=8) (actual time=0.004..0.004 rows=0 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201810_bet_id_idx on bet_parts_201810 p_45 (cost=0.14..20.29 rows=10 width=8) (actual time=0.005..0.005 rows=0 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201811_bet_id_idx on bet_parts_201811 p_46 (cost=0.14..20.29 rows=10 width=8) (actual time=0.004..0.004 rows=0 loops=1)" " Heap Fetches: 0" " -> Index Only Scan using bet_parts_201812_bet_id_idx on bet_parts_201812 p_47 (cost=0.14..20.29 rows=10 width=8) (actual time=0.004..0.004 rows=0 loops=1)" " Heap Fetches: 0" "Total runtime: 763690.622 ms" ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.04.2018, 17:13 |
|
||
|
Джойн на партицированную таблицу
|
|||
|---|---|---|---|
|
#18+
хрень какая-то, у вас условие по bets_history, а партицирована таблица bet_part, соответственно при запросе смотрятся все партиции.. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.04.2018, 17:32 |
|
||
|
Джойн на партицированную таблицу
|
|||
|---|---|---|---|
|
#18+
в постгресе партицирование весьма простенькое, соответственно условие по дате дублируйте и для bet_parts тоже (b.modified или какое там поле). ну и check констрейнты не забудьте, если делали через inherits, а не native partitioning, появившийся 10ке ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.04.2018, 17:34 |
|
||
|
Джойн на партицированную таблицу
|
|||
|---|---|---|---|
|
#18+
bdsm_sql, Собственно в этом и суть запроса, что нужно достать данные не по партицированному полю. Как я уже написал выше, я ожидаю увидеть поиск в индексе в каждой партиции, а не скан этого индекса. Кол-во строк которое сервер ожидает после джойна явно указывает на проблемы со статистикой. При этом вся статистика обновлена вручную, включая parent таблицу. Однако для parent таблицы она все-равно не актуальная (смотрю в pg_stats), ощущение что из-за того что там 0 строк он ее просто игнорирует. Я так понимаю никакой возможности посмотреть так называемую summary stats по parent таблице и всем ее детям нет никакой возможности, хотя мануал говорит о том что такую статистику сервер собирает и использует в случае обхода всего дерева наследования. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.04.2018, 17:46 |
|
||
|
Джойн на партицированную таблицу
|
|||
|---|---|---|---|
|
#18+
Remind, как известно в пж в планировщике не просто конь не валялся , а напротив он оттуда ещё и повыпиливал кучу всего с криками "оно какое-то плохонькое" и повыбрасывал а по части партицирование планер пж запредельно тупой гроб. т.е. все мозги надо прикручивать снаружи. если бы вы партицировались по бет_ид -- то можно было бы что-то из юнионов соорудить, генератором по границам партиций. Код: sql 1. 2. 3. 4. 5. 6. 7. но поскольку вы не по нему пилитесь -- то всё становится много сложнее. я бы попробовал эдак его прощупать: Код: sql 1. 2. 3. 4. 5. авось и без лимита пролезет но понятно, что это запихивание конкретного слона в конкретный угол. при других обстоятельствах оно боком вылезет ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.04.2018, 18:18 |
|
||
|
Джойн на партицированную таблицу
|
|||
|---|---|---|---|
|
#18+
qwwq, Спасибо. Про lateral уже думал, даже писали разработчикам PGNP чтобы добавили его поддержку. Причем он пролазиет при любом лимите, даже 1ккк, но без лимита - нет. Хотелось бы понять существует ли вообще эта статистика для всей иерархии и как ее посмотреть. И нет ли у них там бага, что если parent таблица пустая, то ANALYZE не обновляет в том числе и inheritance stats. https://www.postgresql.org/docs/10/static/sql-analyze.html If the table being analyzed has one or more children, ANALYZE will gather statistics twice: once on the rows of the parent table only, and a second time on the rows of the parent table with all of its children. This second set of statistics is needed when planning queries that traverse the entire inheritance tree. The autovacuum daemon, however, will only consider inserts or updates on the parent table itself when deciding whether to trigger an automatic analyze for that table. If that table is rarely inserted into or updated, the inheritance statistics will not be up to date unless you run ANALYZE manually. If any of the child tables are foreign tables whose foreign data wrappers do not support ANALYZE, those child tables are ignored while gathering inheritance statistics. If the table being analyzed is completely empty, ANALYZE will not record new statistics for that table. Any existing statistics will be retained. ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.04.2018, 20:09 |
|
||
10 сообщений из 10, страница 1 из 1
|
|
Форумы
[новые:0]
/ PostgreSQL
[новые:0]
[игнор отключен]
[закрыт для гостей]
/
Джойн на партицированную таблицу
[новые:0]
