From: DW <dwinner-lists@att.net>
To: pgsql-performance@postgresql.org
Subject: Re: slow queries after ANALYZE
Date: Mon, 14 Nov 2005 13:53:40 -0500
Message-ID: <4378DD34.4080706@att.net> (raw)
In-Reply-To: <4374EDF1.5010709@att.net>
References: <4374EDF1.5010709@att.net>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dwinner-lists@att.net
Subject: Re: slow queries after ANALYZE
In-Reply-To: <4378DD34.4080706@att.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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