PostgreSQL Enum : Search on ENUM type

Question

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?

Answer 1

-- 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.