PostgreSQL Performance – Compound Index Not Delivering Expected Improvements

performancepostgresqlquery-performance

I have a table with a definition something like this….

CREATE TABLE foo (
  A CHAR(6),
  B TIMESTAMP,
  Lots more fields……
)

It contains about 600 million records.

Originally it had these two indexes on it….

CREATE INDEX i1 ON foo (A);
CREATE INDEX i2 ON foo (B);

Most of the queries I run look something like this…

SELECT * FROM foo
WHERE A = ‘ABCDEF’
AND B > ‘some date’
AND B < ‘some other date’

I wanted to improve the performance so ( following advice on this forum) I added this compound index (previous two indexes still remain)….

CREATE INDEX i3 ON foo (A,B);

When I do an EXPLAIN it’s apparent that the new index isn’t being used, but index 'B' is being used.

This leads to some questions…
1) Why isn’t the new index being used?
2) Can I disable my first two indexes to try and force the compound index to be used? (I don’t want to DROP the indexes because they’re likely to days to re-create them)
3) Does the order of the WHERE clauses make any difference? Would a compound index on A,B be any different from one on B,A?
4) how can I improve performance on this query?

Best Answer

Maybe the compound index isn't used because it thinks the single-column index is selective enough that there is no point in adding more selectivity. The way to find out is by seeing the results of EXPLAIN (ANALYZE, BUFFERS) SELECT ...

You can disable the indexes by dropping them inside a transaction you later rollback:

BEGIN;
DROP index i1,i2;
EXPLAIN (ANALYZE, BUFFERS) SELECT....;
ROLLBACK;

Rolling back the index-drop will be nearly instantaneous, it doesn't have to rebuild them. If you run it in production, be aware that the table will be AccessExclusive locked for the duration of the transaction.

You also don't have to experiment with the full 600 million rows. 6 millions is probably plenty to get some valid insight:

create table foo_exp as select * from foo where random()<0.01;
vacuum analyze foo_exp
create index...;
explain (analyze, buffers) ...;

The order in the WHERE clause doesn't matter, but the order in the btree index does. The equality-constrained column should be the leading column in the index, with the range-constrained column after.

For asking performance questions, see https://dba.stackexchange.com/tags/postgresql-performance/info

Related Solutions

PostgreSQL – Order of Columns in Compound Index and Query Order

As a part of my work I maintain a fairly large PostgreSQL database (around 120gb on disk, several multi-million-row tables) and have collected a few tricks on how to speed up the queries. First some comments on your assumptions:

Yes, order is important, but it's only the first one that is really different, the rest are second class indexes.
I'm not sure it will always use both, my guess is that the query planner will use #1, then do something clever with the rest.
I have no experience with GIST.
Yes, add all indexes first, see what is used the most and what gives the best performance.
I would sugest that you try both and measure what works best. Try rewriting the sql with different subqueries, maybe country and time in one, then join with the intersect-query. I have not noticed any performance problem with IN-clauses, as long as the IN-list is not thousands of elements long. My guess is that a few different queries specifically tuned depending on the input criteria available will give the best results.
I would suggest against making a 4-way index. Try creating one and then check the size, they can get really huge. In my experience, four 1-key indexes have been almost as fast as a single 4-way index. A trick that does work nicely for some specific queries are partial indexes, ie something like this:

CREATE INDEX ON table_x (key1, key2, key3) WHERE some_x_column = 'XXXX';

I have created aliases in my .psqlrc-file with queries to help find what indexes to add or remove. Feel free to have a look at them over at GitHub: .psql

I use the :seq_scans and :bigtables a lot, and then \d table_name to get details about the table. Don't forget to reset the statistics after you've done some changes, select pg_stat_reset();

Mysql – Very slow query, not sure if MySQL index is being used

entries needs INDEX(valid, tenent_id)
users needs INDEX(active, user_id)

More discussion (Index Cookbook).

More specifics

LEFT probably prevents the optimizer from picking which table to start with. Assuming the LEFT is important, let's break the query in two:

FROM entries WHERE e.tenant_id=? AND e.valid=1

The link above explains that the optimal index starts with anything with '=', so (tenant_id, valid) in any order. (I suggested one of them.)

Then it moves on to the other table:

LEFT JOIN users u
    ON u.user_id = ...
WHERE u.active=1

Hence, (active, user_id) in any order.

(Sorry, I need to augment the Cookbook with more discussion of JOIN.)

If you could get rid of LEFT, then a different set of indexes might be useful:

(active)
(valid, user_id, tenant_id) -- in any order

Having both pairs of indexes would let the optimizer pick which is better. However, some consolidation is desirable (again, see the link). These two would suffice for either table order. Now the column order is important:

(active, user_id)   -- also handles (active)
(valid, tenant_id, user_id)  -- also handles (valid, tenent_id)

More on your title question of "not sure if index is being used". In the EXPLAIN, NULL and 116710 imply that it is not. But index and Using index imply that it is. The tie-breaker is Using join buffer (Block Nested Loop) which says that it is loading the index into RAM to use it. (This is a case where join_buffer_size matters.)

The 4-column index you had was somewhat useful, but it

did not have all the needed columns (active) to be covering
had an extra column (group_id)
was in a sub-optimal order: the extra column came before the all the needed columns, so valid was not as useful as it could be.

Best Answer

Related Solutions

PostgreSQL – Order of Columns in Compound Index and Query Order

Mysql – Very slow query, not sure if MySQL index is being used

Related Question