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

Поиск
Список
Период
Сортировка
От Pavel Tavoda
Тема Re: [BUGS] BUG #14729: Between operator is slow when same value usedfor low and high margin
Дата
Msg-id CAMRASbLR2-0Q3UtRK2v6O2A5_cUkA8+9GUak7eAwN9EnebtRcQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [BUGS] BUG #14729: Between operator is slow when same value usedfor low and high margin  (Pavel Stehule <pavel.stehule@gmail.com>)
Ответы Re: [BUGS] BUG #14729: Between operator is slow when same value usedfor low and high margin  (Pavel Stehule <pavel.stehule@gmail.com>)
Re: [BUGS] BUG #14729: Between operator is slow when same value used for low and high margin  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения
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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: Re: [BUGS] BUG #14729: Between operator is slow when same value usedfor low and high margin
Следующее
От: sbadyals@gmail.com
Дата:
Сообщение: [BUGS] BUG #14731: ERROR: missing chunk number 0 for toast value 9342315 inpg_toast_2619