3

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(
  id BIGSERIAL NOT NULL PRIMARY KEY ,
  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

SELECT * 
FROM transaction WHERE transaction_currency= 'USD'

This did a sequential scan on the table instead of index

QUERY PLAN
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
SELECT * 
FROM transaction WHERE transaction_currency= 'USD'::currency

This did an index scan

QUERY PLAN
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

Question:

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.