CODEWITHSUNDEEP
sqlpostgresqlindexingquery-optimizationwhere-clause
fasth
fasth

Reputation: 2342

postgres: search by varchar which is enum in java

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?

Upvotes: 0

Views: 152

Answers (1)

GMB
GMB

Reputation: 222442

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';

Upvotes: 1

Related Questions