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

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