Message-ID: <4378DD34.4080706@att.net>
Date: Mon, 14 Nov 2005 13:53:40 -0500
From: DW <dwinner-lists@att.net>
Reply-To: dwinner-lists@att.net
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: slow queries after ANALYZE
References: <4374EDF1.5010709@att.net>
In-Reply-To: <4374EDF1.5010709@att.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.


>