[PERFORM] Speed differences between in executing the same query

I have a weird case of query execution performance here. The query has a date
values in the WHERE clause, and the speed of executing varies by values of
the date. Actualy,
  - for the dates from the range of the last 30 days execution takes aruond
3 min
  - for the dates before the range of the last 30 days execution takes a few
seconds

The query is listed below, with the date in the last 30 days range:

select
    sk2_.code as col_0_0_,
    bra4_.code as col_1_0_,
    st0_.quantity as col_2_0_,
    bat1_.forecast as col_3_0_
from
    TBL_st st0_,
    TBL_bat bat1_,
    TBL_sk sk2_,
    TBL_bra bra4_
where
    st0_.batc_id=bat1_.id
    and bat1_.sku_id=sk2_.id
    and bat1_.bran_id=bra4_.id
    and  not (exists (select
        1
    from
        TBL_st st6_,
        TBL_bat bat7_,
        TBL_sk sk10_
    where
        st6_.batc_id=bat7_.id
        and bat7_.sku_id=sk10_.id
        and bat7_.bran_id=bat1_.bran_id
        and sk10_.code=sk2_.code
        and st6_.date>st0_.date
        and sk10_.acco_id=1
        and st6_.date>='2017-04-20'
        and st6_.date<='2017-04-30'))
    and sk2_.acco_id=1
    and st0_.date>='2017-04-20'
    and st0_.date<='2017-04-30'


and here is the plan for the query with the date in the last  30 days range:

Nested Loop  (cost=289.06..19764.03 rows=1 width=430) (actual
time=3482.062..326049.246 rows=249 loops=1)
  ->  Nested Loop Anti Join  (cost=288.91..19763.86 rows=1 width=433)
(actual time=3482.023..326048.023 rows=249 loops=1)
        Join Filter: ((st6_.date > st0_.date) AND ((sk10_.code)::text =
(sk2_.code)::text))
        Rows Removed by Join Filter: 210558
        ->  Nested Loop  (cost=286.43..13719.38 rows=1 width=441) (actual
time=4.648..2212.042 rows=2474 loops=1)
              ->  Nested Loop  (cost=286.00..6871.33 rows=13335 width=436)
(actual time=4.262..657.823 rows=666738 loops=1)
                    ->  Index Scan using uk_TBL_sk0_account_code on TBL_sk
sk2_  (cost=0.14..12.53 rows=1 width=426) (actual time=1.036..1.084 rows=50
loops=1)
                          Index Cond: (acco_id = 1)
                    ->  Bitmap Heap Scan on TBL_bat bat1_
(cost=285.86..6707.27 rows=15153 width=26) (actual time=3.675..11.308
rows=13335 loops=50)
                          Recheck Cond: (sku_id = sk2_.id)
                          Heap Blocks: exact=241295
                          ->  Bitmap Index Scan on ix_al_batc_sku_id
(cost=0.00..282.07 rows=15153 width=0) (actual time=3.026..3.026 rows=13335
loops=50)
                                Index Cond: (sku_id = sk2_.id)
              ->  Index Scan using ix_al_stle_batc_id on TBL_st st0_
(cost=0.42..0.50 rows=1 width=21) (actual time=0.002..0.002 rows=0
loops=666738)
                    Index Cond: (batc_id = bat1_.id)
                    Filter: ((date >= '2017-04-20 00:00:00'::timestamp
without time zone) AND (date <= '2017-04-30 00:00:00'::timestamp without
time zone))
                    Rows Removed by Filter: 1
        ->  Nested Loop  (cost=2.49..3023.47 rows=1 width=434) (actual
time=111.345..130.883 rows=86 loops=2474)
              ->  Hash Join  (cost=2.06..2045.18 rows=1905 width=434)
(actual time=0.010..28.028 rows=54853 loops=2474)
                    Hash Cond: (bat7_.sku_id = sk10_.id)
                    ->  Index Scan using ix_al_batc_bran_id on TBL_bat bat7_
(cost=0.42..1667.31 rows=95248 width=24) (actual time=0.009..11.045
rows=54853 loops=2474)
                          Index Cond: (bran_id = bat1_.bran_id)
                    ->  Hash  (cost=1.63..1.63 rows=1 width=426) (actual
time=0.026..0.026 rows=50 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 11kB
                          ->  Seq Scan on TBL_sk sk10_  (cost=0.00..1.63
rows=1 width=426) (actual time=0.007..0.015 rows=50 loops=1)
                                Filter: (acco_id = 1)
              ->  Index Scan using ix_al_stle_batc_id on TBL_st st6_
(cost=0.42..0.50 rows=1 width=16) (actual time=0.002..0.002 rows=0
loops=135706217)
                    Index Cond: (batc_id = bat7_.id)
                    Filter: ((date >= '2017-04-20 00:00:00'::timestamp
without time zone) AND (date <= '2017-04-30 00:00:00'::timestamp without
time zone))
                    Rows Removed by Filter: 1
  ->  Index Scan using TBL_bra_pk on TBL_bra bra4_  (cost=0.14..0.16 rows=1
width=13) (actual time=0.003..0.003 rows=1 loops=249)
        Index Cond: (id = bat1_.bran_id)
Planning time: 8.108 ms
Execution time: 326049.583 ms


Here is the same query with the date before the last 30 days range:

select
    sk2_.code as col_0_0_,
    bra4_.code as col_1_0_,
    st0_.quantity as col_2_0_,
    bat1_.forecast as col_3_0_
from
    TBL_st st0_,
    TBL_bat bat1_,
    TBL_sk sk2_,
    TBL_bra bra4_
where
    st0_.batc_id=bat1_.id
    and bat1_.sku_id=sk2_.id
    and bat1_.bran_id=bra4_.id
    and  not (exists (select
        1
    from
        TBL_st st6_,
        TBL_bat bat7_,
        TBL_sk sk10_
    where
        st6_.batc_id=bat7_.id
        and bat7_.sku_id=sk10_.id
        and bat7_.bran_id=bat1_.bran_id
        and sk10_.code=sk2_.code
        and st6_.date>st0_.date
        and sk10_.acco_id=1
        and st6_.date>='2017-01-20'
        and st6_.date<='2017-01-30'))
    and sk2_.acco_id=1
    and st0_.date>='2017-01-20'
    and st0_.date<='2017-01-30'


and here is the plan for the query with the date before the last  30 days
range:

  Hash Join  (cost=576.33..27443.95 rows=48 width=430) (actual
time=132.732..3894.554 rows=250 loops=1)
  Hash Cond: (bat1_.bran_id = bra4_.id)
  ->  Merge Anti Join  (cost=572.85..27439.82 rows=48 width=433) (actual
time=132.679..3894.287 rows=250 loops=1)
        Merge Cond: ((sk2_.code)::text = (sk10_.code)::text)
        Join Filter: ((st6_.date > st0_.date) AND (bat7_.bran_id =
bat1_.bran_id))
        Rows Removed by Join Filter: 84521
        ->  Nested Loop  (cost=286.43..13719.38 rows=48 width=441) (actual
time=26.105..1893.523 rows=2491 loops=1)
              ->  Nested Loop  (cost=286.00..6871.33 rows=13335 width=436)
(actual time=1.159..445.683 rows=666738 loops=1)
                    ->  Index Scan using uk_TBL_sk0_account_code on TBL_sk
sk2_  (cost=0.14..12.53 rows=1 width=426) (actual time=0.035..0.084 rows=50
loops=1)
                          Index Cond: (acco_id = 1)
                    ->  Bitmap Heap Scan on TBL_bat bat1_
(cost=285.86..6707.27 rows=15153 width=26) (actual time=1.741..7.148
rows=13335 loops=50)
                          Recheck Cond: (sku_id = sk2_.id)
                          Heap Blocks: exact=241295
                          ->  Bitmap Index Scan on ix_al_batc_sku_id
(cost=0.00..282.07 rows=15153 width=0) (actual time=1.119..1.119 rows=13335
loops=50)
                                Index Cond: (sku_id = sk2_.id)
              ->  Index Scan using ix_al_stle_batc_id on TBL_st st0_
(cost=0.42..0.50 rows=1 width=21) (actual time=0.002..0.002 rows=0
loops=666738)
                    Index Cond: (batc_id = bat1_.id)
                    Filter: ((date >= '2017-01-20 00:00:00'::timestamp
without time zone) AND (date <= '2017-01-30 00:00:00'::timestamp without
time zone))
                    Rows Removed by Filter: 1
        ->  Materialize  (cost=286.43..13719.50 rows=48 width=434) (actual
time=15.584..1986.953 rows=84560 loops=1)
              ->  Nested Loop  (cost=286.43..13719.38 rows=48 width=434)
(actual time=15.577..1983.384 rows=2491 loops=1)
                    ->  Nested Loop  (cost=286.00..6871.33 rows=13335
width=434) (actual time=0.843..482.864 rows=666738 loops=1)
                          ->  Index Scan using uk_TBL_sk0_account_code on
TBL_sk sk10_  (cost=0.14..12.53 rows=1 width=426) (actual time=0.005..0.052
rows=50 loops=1)
                                Index Cond: (acco_id = 1)
                          ->  Bitmap Heap Scan on TBL_bat bat7_
(cost=285.86..6707.27 rows=15153 width=24) (actual time=2.051..7.902
rows=13335 loops=50)
                                Recheck Cond: (sku_id = sk10_.id)
                                Heap Blocks: exact=241295
                                ->  Bitmap Index Scan on ix_al_batc_sku_id
(cost=0.00..282.07 rows=15153 width=0) (actual time=1.424..1.424 rows=13335
loops=50)
                                      Index Cond: (sku_id = sk10_.id)
                    ->  Index Scan using ix_al_stle_batc_id on TBL_st st6_
(cost=0.42..0.50 rows=1 width=16) (actual time=0.002..0.002 rows=0
loops=666738)
                          Index Cond: (batc_id = bat7_.id)
                          Filter: ((date >= '2017-01-20 00:00:00'::timestamp
without time zone) AND (date <= '2017-01-30 00:00:00'::timestamp without
time zone))
                          Rows Removed by Filter: 1
  ->  Hash  (cost=2.10..2.10 rows=110 width=13) (actual time=0.033..0.033
rows=110 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 14kB
        ->  Seq Scan on TBL_bra bra4_  (cost=0.00..2.10 rows=110 width=13)
(actual time=0.004..0.013 rows=110 loops=1)
Planning time: 14.542 ms
Execution time: 3894.793 ms



Does anyone have an idea why does this happens.
Did anyone had an experience with anything similar?

Thank you very much.
Kind regards, Petar



--
View this message in context:
http://www.postgresql-archive.org/Speed-differences-between-in-executing-the-same-query-tp5960964.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.