I have this example
create type price_schedule_day as enum ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday'); create type price_element as ( price_start time, price_end time, price numeric, schedule_day price_schedule_day ); create table price_schedule ( id bigint generated always as identity primary key, price_element price_element ); insert into price_schedule (price_element) values (array [ row('16:00', '17:00', 1, 'monday')::price_element, row('15:00', '16:00', 22, 'tuesday')::price_element ]); insert into price_schedule (price_element) values (array [ row('15:00', '16:00', 33, 'tuesday')::price_element ]); create index time_idx on price_schedule using gin(price_element); EXPLAIN select pe.price from price_schedule ps, unnest(price_element) pe where pe.schedule_day = 'tuesday'
which create a schedule and inserts two rows into it. I want to do a
SELECT to only
SELECT the array entries that has a price with day to
tuesday. The above actually works fine: it outputs:
SQL Fiddle for this: https://www.db-fiddle.com/f/cxGwfaAiExxRmpBWcWL76c/0.
If i do an
EXPLAIN it doesn't use my index i have created: it just do a
seq scan followed by a
function scan, but i didn't expect that actually because something tells me that postgres can't index the
UNNEST function call. I am pretty new to postgres and especially composite types.
Is there any way for me to index an array of composite types, so i can do the above query but use an index? I am going to have pretty many schedules in the database over time. My backup plan is just to rewrite the array of
price_element to plain rows, and then use indexes on day, start and end.