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
