> 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=1409985loops=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).
Plus: scanning idx_tabla_entidad is more efficient because that index is smaller.
What do you think that idx_entidad_tabla_4 would be the better choice?
Thomas
