Defined an ENUM

CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD');

Using this as a type in a table

CREATE TABLE if not exists transaction(
  amount NUMERIC(35,4) DEFAULT 0.0,
  transaction_currency currency NOT NULL

Created an index on transaction_currency

CREATE INDEX transaction_currency ON transaction(transaction_currency);

Inserted records

INSERT INTO transaction(transaction_currency) VALUES ('EUR'), ('USD'), ('USD');

Tried searching in different ways, one by typecasting and other plain string

FROM transaction WHERE transaction_currency= 'USD'

This did a sequential scan on the table instead of index

Seq Scan on transaction  (cost=0.00..1.61 rows=24 width=15) (actual time=0.721..0.916 rows=24 loops=1)   
  Filter: (transaction_currency = 'USD')     
  Rows Removed by Filter: 25 
Planning time: 2.634 ms 
Execution time: 1.164 ms
FROM transaction WHERE transaction_currency= 'USD'::currency

This did an index scan

Index Scan using transaction_currency on transaction  (cost=0.14..8.56 rows=24 width=15) (actual time=0.062..0.296 rows=25 loops=1)   
  Index Cond: (transaction_currency = 'USD'::currency)
Planning time: 2.581 ms 
Execution time: 0.614 ms


Do we need to explicitly typecast enumerated columns in order to benefit from the index?
If yes, can someone help me understanding how this query work? Can we instruct postgres to automatically typecast the filter to enumerated type?
If not what alternatives we have to this selective performance?

  • Which Postgres version are you using? Works as expected on Postgres 9.6: rextester.com/QAUQ37497 – a_horse_with_no_name Nov 30 '18 at 12:01
  • 1
    [9.6.1] Thanks a lot for the explanation. I do see the same results for the test on rextester. I was more concerned on if usage of index depends on typecasting. It showed different results on small data set. Now that I added more rows, it started running Recheck Condition – nimit Nov 30 '18 at 14:31

Your Answer

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

Browse other questions tagged or ask your own question.