Re: [BUGS] BUG #14729: Between operator is slow when same value usedfor low and high margin

Hi Pavel, results down in text.

On Mon, Jul 3, 2017 at 4:22 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

2017-07-03 12:09 GMT+02:00 <pavel.tavoda@gmail.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