Re: PostgreSQL does not choose my indexes well

On Thursday, April 23, 2020, Thomas Kellerer <shammat(at)gmx(dot)net> wrote:

> > CREATE INDEX idx_tabla_entidad
> > ON public.entidad USING btree
> > (cod_tabla ASC NULLS LAST);
> >
> > CREATE INDEX idx_entidad_tabla_4
> > ON public.entidad USING btree
> > (cod_entidad_tabla ASC NULLS LAST)
> > INCLUDE(cod_entidad, cod_tabla, cod_entidad_tabla)
> > WHERE cod_tabla::bigint = 4;
> >
> >
> > SELECT count(*) from entidad;
> > 34.413.354
> >
> > SELECT count(*) from entidad where cod_tabla = 4;
> > 1.409.985
> >
> >
> > explain (analyze, buffers, format text) select * from entidad where
> cod_tabla = 4
> > Index Scan using idx_tabla_entidad on entidad (cost=0.56..51121.41
> rows=1405216 width=20) (actual time=0.037..242.609 rows=1409985 loops=1)
> > Index Cond: ((cod_tabla)::bigint = 4)
> > Buffers: shared hit=12839
> > Planning Time: 0.158 ms
> > Execution Time: 311.828 ms
> >
> >
> > Why postgresql doesnt use the index idx_entidad_tabla_4?????
>
> Because that index does not contain the column from the WHERE clause as an
> "indexed" column (only as an included column).

But it does match the partials index’s predicate

> Plus: scanning idx_tabla_entidad is more efficient because that index is
> smaller.
>

Really? The absence of 33 million rows in the partial index seems like it
would compensate fully and then some for the extra included columns.

David J.