0

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:

  1. 22
  2. 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.

1
  • 1
    There is no way to index that query. Avoid using arrays and composite types in table definitions. Instead, create two tables with a foreign key relationship. Then the solution will be very simple. Aug 28 at 6:27

0

Your Answer

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