From: | hmidi slim <hmidi(dot)slim2(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Proposition for better performance |
Date: | 2018-03-27 14:42:46 |
Message-ID: | CAMsqVxs8xwxH+f40nUNj+5L9Mo9+9z+xsjFxS0k+ajgG=qOz4Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)*.
E.g:
Given an example 'product-1' which has a product_id = 1 and available
from *27/03/2018
to 31/03/2018*:
*product_id product_name start_date end_date *
* 1 product-1 27-03-2018 31-03-2018*
However if the product is not available in 29/03/2018 I have to divide the
period to 2 intervals:
*product_id product_name start_date end_date
1 product-1 27-03-2018 28-03-2018
1 product-1 30-03-2018 31-03-2018With a
table contains millions of products and if I divide the availability by
intervals I will got a huge number of rows and fetching data will degrade
the performance. Is there any solution to use such as range types or
anything which mentions the unavailable dates.Based on the example
mentioned above, can I mention a data type like range type that take the
start_date and end_date and exclude the unavailable dates?*
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Jungwirth | 2018-03-27 14:44:56 | Re: Check constraints. |
Previous Message | Tom Dearman | 2018-03-27 13:54:44 | logical decoder lsn order between transactions |