Query Plan Improvement with Expression Indexes in PostgreSQL

Bruce Momjian Senior Database Architect

Most people know that Postgres allows the creation of indexes on expressions. This is helpful if you need index lookups of expressions used in where clauses.

However, there is another benefit to expression indexes, and that is optimizer statistics. Not only do expression indexes allow rapid lookups of matching expressions, but they also provide optimizer statistics, which improve row estimates and hence query plans.

Here is an example:

        CREATE TABLE test (x INTEGER);
        INSERT INTO test SELECT x FROM generate_series(1, 100) AS t(x);
        ANALYZE test;
        SELECT COUNT(*) FROM test WHERE x % 2 = 1;
        EXPLAIN SELECT * FROM test WHERE x % 2 = 1;
                             QUERY PLAN
         Seq Scan on test  (cost=0.00..2.50 rows=1 width=4)
           Filter: ((x % 2) = 1)


The optimizer doesn't know the selectivity of the modulus operator, so it initially assumes only one row is returned. Once an expression index is created and analyze statistics generated, the optimizer knows exactly how many rows will be returned:

        CREATE INDEX i_test ON test ((x % 2));
        ANALYZE test;
        EXPLAIN SELECT * FROM test WHERE x % 2 = 1;
                             QUERY PLAN
         Seq Scan on test  (cost=0.00..2.50 rows=50 width=4)
           Filter: ((x % 2) = 1)


Interestingly, the optimizer used expression index statistics, even though the expression index itself was not used. In the example above, the modulus operator is not selective enough to make the index useful, but expression statistics would be useful for more complex queries, e.g. with joins. This method can also be used to create statistics on functions.

It is also possible to create an expression index that generates cross-columns statistics, the benefits of which I mentioned in an earlier blog post. For example, this expression index would supply accurate statistics for state/city combinations, but queries would need to use the exact concatenation construction:

        CREATE INDEX i_customer_state_city ON customer ((state || '|' || city));


It would be nice if there was a way to create expression statistics without the overhead of creating and maintaining indexes.

This post originally appeared on Bruce's personal blog

Bruce MomjianSenior Database Architect

Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value of Postgres advances and new technology enhancements. He is the author of PostgreSQL: Introduction and Concepts, published by Addison-Wesley.