On Thu, Apr 23, 2020 at 7:36 AM Arcadio Ortega Reinoso <arcadio.ortega@xxxxxxxxx> 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
