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.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to