Re: Proposition for better performance

> On Mar 27, 2018, at 4:25 PM, Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> wrote:
>
> On 03/27/2018 11:42 AM, hmidi slim wrote:
>> This is the message that I got:
>> Successfully run. Total query runtime: 2 min.
>> 15000000 rows affected.
>
> Sorry, I don't think this is enough information to suggest anything.
>
> --
> Paul ~{:-)
> pj(at)illuminatedcomputing(dot)com
>

I took the case prsented and ran in a sandbox and

UPDATE 15000000

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

Bitmap Heap Scan on product_availabilities (cost=1156.67..46856.67 rows=75000 width=44) (actual time=5413.792..11367.379 rows=15000000 loops
=1)
Recheck Cond: (during @> '[2018-02-01,2018-04-01)'::daterange)
Heap Blocks: exact=110295
-> Bitmap Index Scan on idx_time (cost=0.00..1137.92 rows=75000 width=0) (actual time=5325.844..5325.844 rows=15000000 loops=1)
Index Cond: (during @> '[2018-02-01,2018-04-01)'::daterange)
Planning time: 0.145 ms
Execution time: 14055.666 ms
(7 rows)

But

considering the update (I did not check bloating or anything but still)

vacuum full product_availabilities;
analyze product_availabilities;

The plan changes to
Seq Scan on product_availabilities (cost=0.00..242647.91 rows=15000033 width=26) (actual time=0.034..7207.697 rows=15000000 loops=1)
Filter: (during @> '[2018-02-01,2018-04-01)'::daterange)
Planning time: 6.701 ms
Execution time: 9238.285 ms

And the runtime does get in the two minutes
time psql -U csidba -d armandp <f.sql > /dev/null
real 2m39.767s
user 1m45.576s
sys 0m12.324s

Not sure if that confirms the OP’s findings but to me a first question would be if the fact that the execution time reported by epxlain analyze does not seem to be even close to the actual run time is expected or not

BTW I was the postgres version reported ? I ran the presented case on 9.5.8

— Armand