> 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
