pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedincreasing query time after analyze
5+ messages / 3 participants
[nested] [flat]
* increasing query time after analyze
@ 2014-02-05 11:50 Katharina Koobs <katharina.koobs@uni-konstanz.de>
0 siblings, 2 replies; 5+ messages in thread
From: Katharina Koobs @ 2014-02-05 11:50 UTC (permalink / raw)
To: pgsql-performance
Hi,
We have a PostgreSQL DB, version 9.3 on a Suse Linux system.
We ran the update from postgresql 8.4 to 9.3.
After importing the database the query time of one sql query is about 30
sec.
After ANALYZE the DB the query time of this sql query is about 45 minutes.
We can see that after analyzing the indexes will no longer be used.
Has anyone an idea why ANALYZE cause this problem?
Thanks a lot for your help!
Katharina
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: increasing query time after analyze
@ 2014-02-05 12:15 Pavel Stehule <pavel.stehule@gmail.com>
parent: Katharina Koobs <katharina.koobs@uni-konstanz.de>
1 sibling, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2014-02-05 12:15 UTC (permalink / raw)
To: Katharina Koobs <katharina.koobs@uni-konstanz.de>; +Cc: pgsql-performance
Hello
2014-02-05 Katharina Koobs <katharina.koobs@uni-konstanz.de>:
> Hi,
>
>
>
> We have a PostgreSQL DB, version 9.3 on a Suse Linux system.
>
> We ran the update from postgresql 8.4 to 9.3.
>
> After importing the database the query time of one sql query is about 30
> sec.
>
> After ANALYZE the DB the query time of this sql query is about 45 minutes.
>
> We can see that after analyzing the indexes will no longer be used.
>
>
>
> Has anyone an idea why ANALYZE cause this problem?
>
yes, it is possible - sometimes due more reasons (some strange dataset or
correlation between columns) a statistics estimations are totally out. And
bad musty statistics can produces better estimations than fresh statistics
please send a "EXPLAIN ANALYZE" output for fast and slow queries.
Regards
Pavel Stehule
>
>
> Thanks a lot for your help!
>
>
>
> Katharina
>
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: increasing query time after analyze
@ 2014-02-05 15:28 Claudio Freire <klaussfreire@gmail.com>
parent: Katharina Koobs <katharina.koobs@uni-konstanz.de>
1 sibling, 0 replies; 5+ messages in thread
From: Claudio Freire @ 2014-02-05 15:28 UTC (permalink / raw)
To: Katharina Koobs <katharina.koobs@uni-konstanz.de>; +Cc: pgsql-performance
On Wed, Feb 5, 2014 at 8:50 AM, Katharina Koobs
<katharina.koobs@uni-konstanz.de> wrote:
> Hi,
>
>
>
> We have a PostgreSQL DB, version 9.3 on a Suse Linux system.
>
> We ran the update from postgresql 8.4 to 9.3.
>
> After importing the database the query time of one sql query is about 30
> sec.
>
> After ANALYZE the DB the query time of this sql query is about 45 minutes.
>
> We can see that after analyzing the indexes will no longer be used.
>
>
>
> Has anyone an idea why ANALYZE cause this problem?
If you just restored a dump, you should do a VACUUM ANALYZE.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* increasing query time after analyze
@ 2014-02-12 08:58 Katharina Koobs <katharina.koobs@uni-konstanz.de>
0 siblings, 1 reply; 5+ messages in thread
From: Katharina Koobs @ 2014-02-12 08:58 UTC (permalink / raw)
To: pgsql-performance
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: increasing query time after analyze
@ 2014-02-12 09:20 Pavel Stehule <pavel.stehule@gmail.com>
parent: Katharina Koobs <katharina.koobs@uni-konstanz.de>
0 siblings, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2014-02-12 09:20 UTC (permalink / raw)
To: Katharina Koobs <katharina.koobs@uni-konstanz.de>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-02-12 09:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-02-05 11:50 increasing query time after analyze Katharina Koobs <katharina.koobs@uni-konstanz.de>
2014-02-05 12:15 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-02-05 15:28 ` Claudio Freire <klaussfreire@gmail.com>
2014-02-12 08:58 increasing query time after analyze Katharina Koobs <katharina.koobs@uni-konstanz.de>
2014-02-12 09:20 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox