Postgres doesn't use expression index for group by

Question

I'm doing a simple group by on a single column with a matching index and it works fine:

create index on contacts (home_city);
select home_city, count(*) 
from contacts 
group by home_city 
limit 50;

Takes 150 ms on an 8 million row table. That's plenty fast enough. EXPLAIN ANALYZE output:

"Limit  (cost=1000.46..9256.24 rows=50 width=17) (actual time=14.201..76.630 rows=50 loops=1)"
"  ->  Finalize GroupAggregate  (cost=1000.46..207395.17 rows=1250 width=17) (actual time=14.200..62.196 rows=50 loops=1)"
"        Group Key: home_city"
"        ->  Gather Merge  (cost=1000.46..207370.17 rows=2500 width=17) (actual time=14.100..76.557 rows=97 loops=1)"
"              Workers Planned: 2"
"              Workers Launched: 2"
"              ->  Partial GroupAggregate  (cost=0.43..206081.59 rows=1250 width=17) (actual time=0.897..56.260 rows=41 loops=3)"
"                    Group Key: home_city"
"                    ->  Parallel Index Only Scan using contacts_6_home_city_idx on contacts_6  (cost=0.43..188620.82 rows=3489654 width=9) (actual time=0.026..28.866 rows=105725 loops=3)"
"                          Heap Fetches: 0"
"Planning Time: 0.099 ms"
"Execution Time: 76.680 ms"

Now I want to do it with a case-insensitive index:

create index on contacts (lower(home_city));
select lower(home_city), count(*) 
from contacts 
group by lower(home_city) 
limit 50;

Takes 3-4 seconds. It appears to be using the wrong index:

"Limit  (cost=215869.40..215882.20 rows=50 width=40) (actual time=4253.233..4253.846 rows=50 loops=1)"
"  ->  Finalize GroupAggregate  (cost=215869.40..216175.66 rows=1197 width=40) (actual time=4253.232..4253.335 rows=50 loops=1)"
"        Group Key: (lower((home_city)::text))"
"        ->  Gather Merge  (cost=215869.40..216148.72 rows=2394 width=40) (actual time=4253.219..4253.796 rows=102 loops=1)"
"              Workers Planned: 2"
"              Workers Launched: 2"
"              ->  Sort  (cost=214869.38..214872.37 rows=1197 width=40) (actual time=4244.566..4244.630 rows=710 loops=3)"
"                    Sort Key: (lower((home_city)::text))"
"                    Sort Method: quicksort  Memory: 88kB"
"                    Worker 0:  Sort Method: quicksort  Memory: 89kB"
"                    Worker 1:  Sort Method: quicksort  Memory: 120kB"
"                    ->  Partial HashAggregate  (cost=214793.22..214808.18 rows=1197 width=40) (actual time=4241.397..4241.658 rows=1021 loops=3)"
"                          Group Key: lower((home_city)::text)"
"                          ->  Parallel Index Only Scan using contacts_6_home_city_idx on contacts_6  (cost=0.43..197344.95 rows=3489654 width=32) (actual time=0.059..2918.355 rows=2778720 loops=3)"
"                                Heap Fetches: 0"
"Planning Time: 0.118 ms"
"Execution Time: 4253.906 ms"

When I delete the index on plain home_city, it does a full table scan and takes 6 seconds.

How do I get it to use the right index? What am I missing here?

(I'm using Postgres 11.5).

Answer 1

There is this code in check_index_only in src/backend/optimizer/path/indxpath.c:

/*
 * check_index_only
 *      Determine whether an index-only scan is possible for this index.
 */
static bool
check_index_only(RelOptInfo *rel, IndexOptInfo *index)
{

[...]

    /*
     * Construct a bitmapset of columns that the index can return back in an
     * index-only scan.  If there are multiple index columns containing the
     * same attribute, all of them must be capable of returning the value,
     * since we might recheck operators on any of them.  (Potentially we could
     * be smarter about that, but it's such a weird situation that it doesn't
     * seem worth spending a lot of sweat on.)
     */
    for (i = 0; i < index->ncolumns; i++)
    {
        int         attno = index->indexkeys[i];

        /*
         * For the moment, we just ignore index expressions.  It might be nice
         * to do something with them, later.
         */
        if (attno == 0)
            continue;

So if I read that right, expressions are ignored when an index only scan is considered.

I don't think that's a matter of principle, it is just not implemented.

From PostgreSQL v12 on, you could use a case-insensitive ICU collation to get what you want:

CREATE COLLATION und_ci (
   LOCALE = 'und-u-ks-level2',
   PROVIDER = icu,
   DETERMINISTIC = FALSE
);

Then you'd have to change the column to use that collation:

ALTER TABLE contacts ALTER home_city TYPE text COLLATE und_ci;

This index can be used for case insensitive searches, and you can use it for an index only scan too.

Answer 2

To expand on Laurenz Albe answer

Indexes expressions will be used on a filter clause WHERE pg 11 documentation

select lower(home_city), count(*) 
from contacts where lower(home_city) = 'indianapolis'
group by home_city

this will use the lower() index

Missing the where clause an index only scan is not implement here is the documentation index only scan which has additional limitations to look out for..