0

I have a table which I query on the varchar column. It can be one of the very few values possible (it's a simple java enum).

The SQL:

SELECT *
FROM   activation 
WHERE  registration = '1312313'
       AND status = 'WORKING';

Here I run EXPLAIN ANALYZE EXECUTE https://explain.depesz.com/s/Whuq, as it seems the problem is in the filtering by status. There is an index on the registration column.

Would it be reasonable to add an index on the status column or it's overkill here? If yes, how could I optimize the given query?

GMB
  • 216,147
  • 25
  • 84
  • 135
fasth
  • 2,232
  • 6
  • 26
  • 37

1 Answers1

1

For this query, you want a compound index on the columns that appear in the where clause. It is usually a good idea to put the most restrictive criteria first (here, I understand this is registration):

create index activation_idx on activation(registration, status);

If you always filter on the same status value, then a partial index might be at least as efficient, and less expensive:

create index activation_idx_partial 
    on activation(registration) 
    where status = 'WORKING';
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Firstly, never knew about the partial index, but I filter on the few `status` value. Secondly, it really depends on the data on which criteria are more restrictive. It worked well when the initial filtering by `registration` returned just a few records, but it turned out that it's not always so, that's why I puzzled with it in the first place. So, I need a query which is not dependable on the order of predicates. Probably the compound index will be a solution. Do I need to remove my old `registration` only index? Or it's ok to have them both? – fasth Jun 30 '20 at 20:40
  • @fasth: from technical perspective having both indexes is OK (Postgres will pick the one it prefers). But bottom line, there is no point keeping an index that is not used (it costs storage and incurs more work for the database on DML statements) - so, unless you have another query that uses the index, just drop it. – GMB Jun 30 '20 at 20:42
  • @GBM: based on the answer here https://stackoverflow.com/questions/29217030/compound-index-with-three-keys-what-happens-if-i-query-skipping-the-middle-one, the efficiency won't be hit badly if `registration` filtering is very selective and I will use this compound index in other places instead of plain one (`create index activation_reg on activation(registration)`)? – fasth Jun 30 '20 at 20:49