Re: [PERFORM] Speed differences between in executing the same query

[plukovic]

Thank you very much Tom. This is very helpful.



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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Speed differences between in executing the same query

[Tom Lane]

plukovic  writes:
> I have a weird case of query execution performance here.

My first thought is that you are getting a bad plan because of this
estimation error:

> ->  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)

That rowcount estimate is off by 50X, resulting in 50X errors for the
joins above it too, and in misguided choices of nestloops when some
other join method would be better.  Probably things would improve with
a better estimate.  Maybe you need to increase the stats target for
that table ... or maybe it just hasn't been ANALYZEd lately?

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Speed differences between in executing the same query

[plukovic]

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_