Re: Postgres using the wrong index index

Thanks for your response, Justin!

Here's <https://explain.depesz.com/s/kCvN> the plan if we disable the
custom_2 index. It uses the index I expect and it's much faster.

Here's <https://explain.depesz.com/s/KBgG> a plan if we disable index
scans. It uses both indexes and is much faster.

Here are the stats you asked for:

[image: image.png]

And here are the table stats for
other_events_1004175222_pim_core_custom_2_8e65d072fbdd_idx and
other_events_1004175222_pim_evdef_67951aef14bc_idx:

[image: image.png]

Thanks again for your help!

On Wed, Aug 11, 2021 at 8:38 AM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> On Tue, Aug 10, 2021 at 12:47:20PM -0400, Matt Dupree wrote:
> > Here's the plan: https://explain.depesz.com/s/uNGg
> >
> > Note that the index being used is
>
> Could you show the plan if you force use of the intended index ?
> For example by doing begin; DROP INDEX indexbeingused; explain thequery;
> rollback;
> Or: begin; UPDATE pg_index SET indisvalid=false WHERE
> indexrelid='indexbeingused'::regclass explain thequery; rollback;
>
> Could you show the table statistics for the time, user_id, and type
> columns on
> all 4 tables ?
> | SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV,
> tablename, attname, inherited, null_frac, n_distinct,
> array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1)
> n_hist, correlation FROM pg_stats WHERE attname='...' AND tablename='...'
> ORDER BY 1 DESC;
>
> It might be interesting to see both query plans when index scans are
> disabled
> and bitmap scan are used instead (this might be as simple as begin; SET
> LOCAL
> enable_indexscan=off ...; rollback;);
>
> > Also note that these child tables have 100s of partial indexes. You
> > can find history on why we have things set up this way here
> > <
> https://heap.io/blog/running-10-million-postgresql-indexes-in-production>.
>
> I have read it before :)
>
> > SELECT relname, relpages, reltuples, relallvisible, pg_table_size(oid)
> > FROM pg_class WHERE relname = 'other_events_1004175222';
>
> Could you also show the table stats for the two indexes ?
>
> One problem is that the rowcount estimate is badly off:
> | Index Scan using
> other_events_1004175222_pim_core_custom_2_8e65d072fbdd_idx on
> public.other_events_1004175222 (cost=0.57..1,213,327.64 rows=1,854,125
> width=32) (actual time=450.588..29,057.269 rows=23 loops=1)
>
> To my eyes, this looks like a typo ; it's used in the index predicate as
> well
> as the query, but maybe it's still relevant ?
> | #close_onborading
>
> --
> Justin
>

--

K. Matt Dupree

Data Science Engineer
321.754.0526 | matt(dot)dupree(at)heap(dot)io