Обсуждение: increasing query time after analyze

Поиск
Список
Период
Сортировка

increasing query time after analyze

От
"Katharina Koobs"
Дата:

Hi,

 

We have still problems with our query time.

After restoring the database the query time is about one minute.

After an analyze the query time is about 70 minutes.

We could discover one table which cause the problem.

After analyzing this table the query time increases.

We have made an explain plan before and after analyzing the table cifx.

 

The plans can be found on

 

After analyze:

explain.depesz.com/s/HuZ

 

Before analyze:

explain.depesz.com/s/XWF

 

Thanks a lot for your help!

 

 

 

 

Re: increasing query time after analyze

От
Pavel Stehule
Дата:

2014-02-12 9:58 GMT+01:00 Katharina Koobs <katharina.koobs@uni-konstanz.de>:
explain.depesz.com/s/HuZ

fast query is fast due intesive use a hashjoins

but you can see 

Hash Left Join  (cost=9343.05..41162.99 rows=6889 width=1350) (actual time=211.767..23519.296 rows=639137 loops=1)
a estimation is out. Is strange so after ANALYSE a estimation is worse

Nested Loop Left Join (cost=1122.98..28246.98 rows=1 width=1335) (actual time=33.222..14631581.741 rows=639137 loops=1)

So it looks some in data is strange - probably dependency between columns: sos_stg_aggr.stichtag = sos_stichtag.tid, sos_stg_aggr.stuart = cifx.apnr

Hash Join (cost=1121.04..24144.02 rows=57 width=339) (actual time=2.407..11157.151 rows=639221 loops=1)

  • Hash Cond: (sos_stg_aggr.stuart = cifx.apnr)

Nested loop based plan is extremely sensitive to this wrong estimation.
 
You can try:

* penalize nested loop - set enable_nested_loop to off; -- for this query
* divide this query to more queries - store result temporary table and analyze (fix wrong estimation)
* maybe you can increase a work_mem

Regards

Pavel Stehule