Postgres Pro
Downloads
Home   >   mailing lists

Re: Proposition for better performance - Mailing list pgsql-general

From hmidi slim
Subject Re: Proposition for better performance
Date
Msg-id CAMsqVxtg-qZizp==Y3hO-Js7buHMqZJj8MiuYuvUUVoJHo+YOg@mail.gmail.com
Whole thread Raw
In response to Proposition for better performance  (hmidi slim <hmidi.slim2@gmail.com>)
Responses Re: Proposition for better performance  (Paul Jungwirth <pj@illuminatedcomputing.com>)
List pgsql-general
Tree view
I update the example:
    create table product_availabilities(product_id integer, start_date date, end_date date);

    insert into product_availabilities(product_id, start_date, end_date)
    select a, '2018-01-01', '2018-05-03' from generate_series(1,15000000) as a
   
   
    alter table product_availabilities add column during daterange;
   
    update product_availabilities set during = daterange(start_date, end_date);

    CREATE INDEX idx_time ON product_availabilities USING gist(during); 

    explain analyze SELECT product_id, start_date, end_date, during
    FROM product_availabilities
    WHERE during @> daterange('2018-02-01', '2018-04-01')

Query plan:
Bitmap Heap Scan on product_availabilities  (cost=33728.79..236086.04 rows=878500 width=26) (actual time=2775.058..5792.842 rows=15000000 loops=1)
  Recheck Cond: (during @> '[2018-02-01,2018-04-01)'::daterange)
  Heap Blocks: exact=31040 lossy=79255
  ->  Bitmap Index Scan on idx_time  (cost=0.00..33509.17 rows=878500 width=0) (actual time=2767.262..2767.262 rows=15000000 loops=1)
        Index Cond: (during @> '[2018-02-01,2018-04-01)'::daterange)
Planning time: 0.063 ms
Execution time: 6408.456 ms


SELECT product_id, start_date, end_date, during
    FROM product_availabilities
    WHERE during @> daterange('2018-02-01', '2018-04-01')
returns  query runtime: 2min

pgsql-general by date:

Previous
From: Moreno Andreo
Date:
Subject: Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade
Next
From: Adrian Klaver
Date:
Subject: Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade