Re: Postgres using the wrong index index

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(at)telsasoft(dot)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(dot)dupree(at)heap(dot)io