Re: Proposition for better performance

On 03/27/2018 07:42 AM, hmidi slim wrote:
> Hi,
> I'm trying to create an availability table for existing products. I'm 
> fetching the suitable schema to design in order to get good performance 
> when I fetch products in a table contains millions of rows.
> I think to make a schema like this:
> *create table availability (product_id integer, product_name 
> varchar(255), start_date date, end_date date)*.

I would use a tstzrange (or daterange) instead of separate start_date 
and end_date columns. Then you can create an exclusion constraint that 
has `EXCLUDE USING gist (id WITH =, available_during WITH &&)`. That 
will automatically add a GiST index on those columns that should help 
with faster lookups. (It will also prevent contradictions where a 
product has two overlapping rows.)

You didn't mention the queries you want to be fast, but that index 
should cover the reasonable ones I think.

 > Is there any solution to use such as range types or anything which
 > mentions the unavailable dates.

For any product, there should be the same number of unavailable periods 
as available, right---or often one more? So I don't see any performance 
benefit in doing it that way, and it certainly seems less intuitive to 
store when something is *not* available.

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com