enums, PostgreSQL - rextester

Language: Layout:

drop table if exists transactions;
drop type if exists currency;

CREATE TYPE currency AS ENUM('GBP', 'EUR', 'USD');
CREATE TABLE transactions
(
  id BIGSERIAL NOT NULL PRIMARY KEY ,
  amount NUMERIC(35,4) DEFAULT 0.0,
  transaction_currency currency NOT NULL
);

insert into transactions (amount, transaction_currency)
select random() * 10000, 
       'GBP'
from generate_series(1,5000);
       
insert into transactions (amount, transaction_currency)
select random() * 10000, 
       'USD'
from generate_series(1,15000);
       
insert into transactions (amount, transaction_currency)
select random() * 10000, 
       'EUR'
from generate_series(1,100);
       
CREATE INDEX transaction_currency ON transactions(transaction_currency);

analyze transactions;

-- uses the index because only a few rows are returned
explain analyze
select *
from transactions
where transaction_currency = 'EUR';

-- uses the index because only a few rows are returned
explain analyze
select *
from transactions
where transaction_currency = 'GBP';

-- does not use the index because the majority of all rows are returned
explain analyze
select *
from transactions 
where transaction_currency = 'USD';


absolute service time: 0,95 sec