Skip site navigation (1) Skip section navigation (2)

Re: slow queries after ANALYZE

From: DW <dwinner-lists(at)att(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow queries after ANALYZE
Date: 2005-11-14 18:53:40
Message-ID: 4378DD34.4080706@att.net (view raw or flat)
Thread:
Lists: pgsql-performance
DW wrote:
> Hello,
> 
> I'm perplexed. I'm trying to find out why some queries are taking a long 
> time, and have found that after running analyze,  one particular query 
> becomes slow.
> 
> This query is based on a view that is based on multiple left outer joins 
> to merge data from lots of tables.
> 
> If I drop the database and reload it from a dump, the query result is 
> instaneous (less than one second).
> 
> But after I run analyze, it then takes much longer to run -- about 10 
> seconds, give or take a few depending on the hardware I'm testing it on.
> Earlier today, it was taking almost 30 seconds on the actual production 
> server -- I restarted pgsql server and the time got knocked down to 
> about 10 seconds -- another thing I don't understand.
> 
> I've run the query a number of times before and after running analyze, 
> and the problem reproduces everytime. I also ran with "explain", and saw 
> that the costs go up dramatically after I run analyze.
> 
> I'm fairly new to postgresql and not very experienced as a db admin to 
> begin with, but it looks like I'm going to have to get smarter about 
> this stuff fast, unless it's something the programmers need to deal with 
> when constructing their code and queries or designing the databases.
> 
> I've already learned that I've commited the cardinal sin of configuring 
> my new database server with RAID 5 instead of something more sensible 
> for databases like 0+1, but I've been testing out and replicating this 
> problem on different hardware, so I know that this issue is not the 
> direct cause of this.
> 
> Thanks for any info. I can supply more info (like config files, schemas, 
> etc.) if you think it might help. But I though I would just describe the 
> problem for starters.
> 
> -DW
> 
Well, for whatever it's worth, on my test box, I upgraded from postgreql 
7.4.9 to 8.1, and that seems to make all the difference in the world.

These complex queries are instantaneous, and the query planner when I 
run EXPLAIN ANALYZE both before and after running ANALYZE displays 
results more in line with what is expected (< 60ms).

Whatever changes were introduced  in 8.x seems to make a huge improvment 
in query performance.




> 


In response to

pgsql-performance by date

Next:From: David BorehamDate: 2005-11-14 21:41:29
Subject: Re: Postgres recovery time
Previous:From: RonDate: 2005-11-14 15:57:42
Subject: Re: 8.x index insert performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group