PostgreSQL Enum : Search on ENUM type

4 votes

1 answer

5651 views

postgresql performance enum postgresql-performance

                          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?

                        

Asked by Nimit (41 rep)

Nov 30, 2018, 11:04 AM
Last activity: May 5, 2025, 04:04 AM

PostgreSQL Enum : Search on ENUM type

Related Questions