Re: BUG #14729: Between operator is slow when same value used for low and high margin

2017-07-03 16:39 GMT+02:00 Pavel Tavoda <pavel(dot)tavoda(at)gmail(dot)com>:

> Hi Pavel, results down in text.
>
> On Mon, Jul 3, 2017 at 4:22 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> Hi
>>
>> 2017-07-03 12:09 GMT+02:00 <pavel(dot)tavoda(at)gmail(dot)com>:
>>
>>> SELECT count(*) FROM contractportfolio cp JOIN contract co ON
>>> cp.contract =
>>> co.id WHERE validfor between '2017-05-30' AND '2017-05-31';
>>> Time: 0.473
>>>
>> Aggregate (cost=110320.00..110320.01 rows=1 width=0) (actual
> time=520.304..520.304 rows=1 loops=1)
> -> Hash Join (cost=426.27..110244.02 rows=30393 width=0) (actual
> time=5.852..509.223 rows=40844 loops=1)
> Hash Cond: (cp.contract = co.id)
> -> Seq Scan on contractportfolio cp (cost=0.00..109171.90
> rows=30393 width=8) (actual time=0.007..471.669 rows=40844 loops=1)
> Filter: ((validfor >= '2017-05-30'::date) AND (validfor <=
> '2017-05-31'::date))
> Rows Removed by Filter: 2946433
> -> Hash (cost=336.12..336.12 rows=7212 width=8) (actual
> time=5.833..5.833 rows=7198 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 197kB
> -> Seq Scan on contract co (cost=0.00..336.12 rows=7212
> width=8) (actual time=0.006..2.856 rows=7198 loops=1)
> Planning time: 0.333 ms
> Execution time: 520.343 ms
>
>
>
>
>> SELECT count(*) FROM contractportfolio cp JOIN contract co ON cp.contract
>>> =
>>> co.id WHERE validfor between '2017-05-31' AND '2017-05-31';
>>> Time: 19.172
>>>
>> Aggregate (cost=109598.17..109598.18 rows=1 width=0) (actual
> time=87769.576..87769.577 rows=1 loops=1)
> -> Nested Loop (cost=0.00..109598.16 rows=1 width=0) (actual
> time=1.949..87759.415 rows=20426 loops=1)
> Join Filter: (cp.contract = co.id)
> Rows Removed by Join Filter: 147005922
> -> Seq Scan on contractportfolio cp (cost=0.00..109171.90 rows=1
> width=8) (actual time=0.009..486.918 rows=20426 loops=1)
> Filter: ((validfor >= '2017-05-31'::date) AND (validfor <=
> '2017-05-31'::date))
> Rows Removed by Filter: 2966851
> -> Seq Scan on contract co (cost=0.00..336.12 rows=7212 width=8)
> (actual time=0.001..2.046 rows=7198 loops=20426)
> Planning time: 0.319 ms
> Execution time: 87769.621 ms
>
> STUNNING!!!!
>
>
>>
>>> SELECT count(*) FROM contractportfolio cp JOIN contract co ON
>>> cp.contract =
>>> co.id WHERE validfor = '2017-05-31';
>>> Time: 0.467
>>>
>> Aggregate (cost=102402.86..102402.87 rows=1 width=0) (actual
> time=492.645..492.646 rows=1 loops=1)
> -> Hash Join (cost=426.27..102352.37 rows=20197 width=0) (actual
> time=5.873..486.873 rows=20426 loops=1)
> Hash Cond: (cp.contract = co.id)
> -> Seq Scan on contractportfolio cp (cost=0.00..101496.91
> rows=20197 width=8) (actual time=0.009..463.063 rows=20426 loops=1)
> Filter: (validfor = '2017-05-31'::date)
> Rows Removed by Filter: 2966851
> -> Hash (cost=336.12..336.12 rows=7212 width=8) (actual
> time=5.856..5.856 rows=7198 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 197kB
> -> Seq Scan on contract co (cost=0.00..336.12 rows=7212
> width=8) (actual time=0.005..2.835 rows=7198 loops=1)
> Planning time: 0.325 ms
> Execution time: 492.686 ms
>

It looks like unanalyzed data - the system thinking so there are not any
data. Try to run ANALYZE more frequently or after any significant change of
table

Regards

Pavel