Re: PostgreSQL does not choose my indexes well

> 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).
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