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