From: DW <dwinner-lists@att.net>
To: pgsql-performance@postgresql.org
Subject: slow queries after ANALYZE
Date: Fri, 11 Nov 2005 14:16:01 -0500
Message-ID: <4374EDF1.5010709@att.net> (raw)
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
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: <4374EDF1.5010709@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