Group by query not using index

Question

This is my query -

select bill, render, count (*)
from dx
group by bill, render

This is the index I created for it -

CREATE INDEX bill_render ON dx (bill, render);

Why is my query not using the index with and without the count(*)?

"Finalize GroupAggregate  (cost=70441673.89..89334654.09 
   rows=61437331 width=27)"
    "  Group Key: bill, render"
    "  ->  Gather Merge  (cost=70441673.89..87798720.82 rows=122874662 width=27)"
    "        Workers Planned: 2"
    "        ->  Partial GroupAggregate  
    (cost=70440673.86..73614935.97 rows=61437331 width=27)"
        "              Group Key: bill, render"
    "              ->  Sort  (cost=70440673.86..71080646.06 rows=255988880 width=19)"
    "                    Sort Key: bill, render"
    "                    ->  Parallel Seq Scan on dx (cost=0.00..18940581.80 rows=255988880 width=19)"

score 0 · Accepted Answer · answered Aug 29 '19 at 16:19

0

The reason is that it cannot perform an index only scan, and a normal index scan (that has to fetch the table rows to check for visibility) would be slower than a sequential scan.

If you want to get an index only scan in PostgreSQL, you have to make sure that most table blocks are marked “all-visible” in the visibility map. Then PostgreSQL can skip the expensive heap fetch.

The visibility map is maintained by VACUUM, so run that on the table to improve the performance.

answered Aug 29 '19 at 16:19

Laurenz Albe

209,280
17
206
263

If after running `VACUUM`, it was still not using the index, would it be better to use the plan it suggested or keep trying to get it to use the index? – Aaron Aug 29 '19 at 16:50
You said the performance was better after `VACUUM`, was it not? – Laurenz Albe Aug 29 '19 at 20:13

Group by query not using index

1 Answers1