Postgres Pro
Downloads
Home   >   mailing lists

Thread: increasing query time after analyze

increasing query time after analyze

From
"Katharina Koobs"
Date:

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

 

 

Re: increasing query time after analyze

From
Pavel Stehule
Date:
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

 

 


Re: increasing query time after analyze

From
Claudio Freire
Date:
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.