On Thursday, April 23, 2020, Thomas Kellerer <
shammat@gmx.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.