Re: PostgreSQL does not choose my indexes well

On Thu, Apr 23, 2020 at 7:36 AM Arcadio Ortega Reinoso <
arcadio(dot)ortega(at)gmail(dot)com> wrote:

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

In order to read 1409985 / 12839 = 109 rows per buffer page, the table must
be extraordinarily well clustered on this index. That degree of clustering
is going to steal much of the thunder from the index-only scan. But in my
hands, it does still prefer the partial index with index-only scan by a
cost estimate ratio of 3 to 1 (despite it actually being slightly slower)
so I don't know why you don't get it being used.

This was how I populated the table:

insert into entidad select id, floor(random()*25)::int,
floor(random()*10000000)::int from generate_series(1,34000000) f(id);
cluster entidad USING idx_tabla_entidad ;

0.3 seconds for 1.4 million rows is pretty good. How much better are you
hoping to get by micro-managing the planner?

To figure it out, it might help to see the explain (analyze, buffers,
format text) of the plan you want it to use. But the only way I see to do
that is to drop the other index.

If you don't want to "really" drop the index, you can drop it in a
transaction, run the "explain (analyze, buffers, format text)" query, and
rollback the transaction. (Note this will lock the table for the entire
duration of the transaction, so it is not something to do cavalierly in
production)

Cheers,

Jeff