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 <pryzby@xxxxxxxxxxxxx> 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
--
