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
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.
