0

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?

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.