Index for a WHERE clause with a date or more

Question

I am using Postgres 9.1 and have a terribly slow executable request.

Request:

Explain Analyze SELECT COUNT(DISTINCT email) FROM "invites" WHERE ( created_at < '2012-10-10 21:08:05.259200' AND invite_method = 'email' AND accept_count = 0 AND reminded_count < 3 AND (last_reminded_at IS NULL OR last_reminded_at < '2012-10-10 21:08:05.261483')) 

Results:

 Aggregate (cost=19828.24..19828.25 rows=1 width=21) (actual time=11395.903..11395.903 rows=1 loops=1) -> Seq Scan on invites (cost=0.00..18970.57 rows=343068 width=21) (actual time=0.036..353.121 rows=337143 loops=1) Filter: ((created_at < '2012-10-10 21:08:05.2592'::timestamp without time zone) AND (reminded_count < 3) AND ((last_reminded_at IS NULL) OR (last_reminded_at < '2012-10-10 21:08:05.261483'::timestamp without time zone)) AND ((invite_method)::text = 'email'::text) AND (accept_count = 0)) Total runtime: 11395.970 ms 

As you can see, it takes about 11 seconds. How can I add an index to optimize query performance?

Answer 1

Simple indexing "everything", as @Jim advises, is not a very effective strategy. Indexes do bear the cost of maintaining and combining many individual indexes, which are more expensive (for maintenance and query) than a single individual index. It always depends on your overall situation.

The cost of indexes is low for read-only or rarely written tables, but high for volatile tables with a lot of write operations. An additional drawback is that indexes prohibit HOT-Updates (Heap Only). More on this answer.

If performance is important for a particular query, a partial multi-column index would be a good strategy. It specializes, but much cheaper and faster than individual indexes on all columns involved. Rule of thumb: ...

• put columns for volatile conditions (change between queries) in the index.
• use the stability conditions (the same for each query) in the WHERE to narrow the index section.

Judging by the column names (due to lack of information), accept_count = 0 seems to be the most selective (and stable) filter here, and created_at and last_reminded_at probably continue to change. So maybe something like this:

 CREATE INDEX invites_special_idx ON invites (created_at, last_reminded_at) WHERE accept_count = 0 AND invite_method = 'email' AND reminded_count < 3; 

Sorting created_at and last_reminded_at in ascending order as requested is ideal - this is the default. Thus, the system can get all the relevant rows in one scan from the top of the index. It must be very fast.

As we discussed in one of your previous questions, this can be additional help for grouping a table by index. Be sure to read the CLUSTER manual.
As @Craig provided, you cannot CLUSTER on a partial index. Since CLUSTER is a one-time operation (effects degrade with later write operations), you can get around this limitation by creating a full index, CLUSTER table, and omit the index again. How:

 CREATE INDEX invites_special_idx2 ON invites (created_at, last_reminded_at); CLUSTER invites USING invites_special_idx2; DROP INDEX invites_special_idx2; 

CLUSTER is only useful when there are no other important queries with conflicting data distribution requirements.

PostgreSQL 9.2 has several new features that are likely to make your query faster. In particular, only for indexing (the first element in the release notes ). You might want to consider updating.

Answer 2

You must add indexes for email, created_at, invite_method, accept_count, reminded_count and last_reminded. As a rule, everything that is to the left of the WHERE statement.