Justin, The rowcount estimate for the time column is bad for all these plans - do > you > know why ? You're using inheritence - have you analyzed the parent tables > recently ? >
Yes. I used ANALYZE before posting, as it's one of the "things to try" listed in the slow queries wiki. I even ran the queries immediately after analyzing. No difference. Can you say more about why the bad row estimate would cause Postgres to use the bigger index? I would expect Postgres to use the smaller index if it's over-estimating how many rows will be returned. Mladen, You know that you can use pg_hint_plan extension? That way you don't have > to disable indexes or set session parameters. > Thanks for the tip! I didn't know you could use pg_hint_plan to force the use of certain indexes. For now, I'd like to avoid hinting and fix the underlying issue. On Wed, Aug 11, 2021 at 11:45 PM Justin Pryzby <pry...@telsasoft.com> wrote: > The rowcount estimate for the time column is bad for all these plans - do > you > know why ? You're using inheritence - have you analyzed the parent tables > recently ? > > | Index Scan using other_events_1004175222_pim_evdef_67951aef14bc_idx on > public.other_events_1004175222 (cost=0.28..1,648,877.92 rows=1,858,891 > width=32) (actual time=1.008..15.245 rows=23 loops=1) > | Index Cond: ((other_events_1004175222."time" >= > '1624777200000'::bigint) AND (other_events_1004175222."time" <= > '1627369200000'::bigint)) > > -- > Justin > -- K. Matt Dupree Data Science Engineer 321.754.0526 | matt.dup...@heap.io
