4

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?

2
  • Which Postgres version are you using? Works as expected on Postgres 9.6: rextester.com/QAUQ37497
    – user1822
    Commented Nov 30, 2018 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
    Commented Nov 30, 2018 at 14:31

1 Answer 1

0
-- Create the ENUM type
CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD');

-- Create the transaction 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
);

-- Create the custom operator
CREATE OPERATOR = (
  PROCEDURE = public.enum_equal,
  LEFTARG = public.currency,
  RIGHTARG = text,
  COMMUTATOR = =,
  NEGATOR = <>
);

-- Create the function for typecasting
CREATE OR REPLACE FUNCTION public.enum_equal(
    public.currency,
    text
)
RETURNS boolean
LANGUAGE sql
AS $function$
    SELECT $1::text = $2
$function$;

-- Create the index on transaction_currency
CREATE INDEX transaction_currency ON transaction(transaction_currency);

-- Insert records into the transaction table
INSERT INTO transaction (transaction_currency)
VALUES ('EUR'), ('USD'), ('USD');

-- Perform the query using the custom operator for automatic typecasting
SELECT *
FROM transaction
WHERE transaction_currency = 'USD';

The index on transaction_currency is created, and records are inserted into the transaction table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.