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