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(at)illuminatedcomputing(dot)com