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:
- 22
- 33
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.