Обсуждение: Join plan issue with inherited tables

Поиск
Список
Период
Сортировка

Join plan issue with inherited tables

От
Wells Oliver
Дата:
Hey everyone, maybe you can push me in the right direction. I have a query joining to a parent table and two records within the same child table are using drastically different plans, one of which examines million and millions of rows.

I have a Large Table called mpd. It's partitioned into child tables based on year & week. So mpd is parent, mpd_y2018_wk1, etc. Each child table has roughly 100m rows.

I am attempting to write a query, roughly like

select * from p
join mpd
   on mpd.pid = p.pid
   and mpd.timestamp = p.timestamp
   and mpd.id = 3
where p.gid = 123;

The planner for ANALYZE for gid 123 uses a number of indexes and sequence scans. gid 123 is in the same child table (mpd_y2018_wk25) as gid 456, but gid 456 uses a ton of sequence scans over all child instances of mpd, like so:

->  Seq Scan on mpd_yr2017_wk14 mpd_b1_1  (cost=0.00..2312832.62 rows=8987491 width=28)
    Filter: (id = 3)
->  Seq Scan on mpd_yr2017_wk13 mpd_b1_2  (cost=0.00..308961.34 rows=1007137 width=28)
    Filter: (id = 3)
->  Seq Scan on mpd_yr2016_wk14 mpd_b1_3  (cost=0.00..1931995.80 rows=7402426 width=28)
    Filter: (id = 3)

So it's examining a bazillion rows across all tables (and essentially will never complete), whereas a different gid (in the same child table!)  uses indexes and executes in about 500ms.

I know this is vague, but I just don't know where to look next. I can't figure out why these query plans are so utterly different with a different identifier.

--