Re: slow queries after ANALYZE

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.

>