On 26-8-2005 15:05, Richard Huxton wrote:
Arjen van der Meijden wrote:

I left all the configuration-stuff to the defaults since changing values didn't seem to impact much. Apart from the buffers and effective cache, increasing those made the performance worse.

I've not looked at the rest of your problem in detail, but using the default configuration values is certainly not going to help things. In particular effective_cache is supposed to tell PG how much memory your OS is using to cache data.

Read this through and make sure your configuration settings are sane, then it might be worthwhile looking in detail at this particular query.

Thanks for the advice. But as said, I tried such things. Adjusting shared buffers to 5000, 10000 or 15000 made minor improvements. But adjusting the effective_cache was indeed very dramatic, to make matters worse! Changing the random_page_cost to 2.0 also made it choose another plan, but still not the fast plan.

The machine has 1GB of memory, so I tested for effective cache size 10000, 20000, 40000, 60000 and 80000. The "600ms"-plan I'm talking about will not come up with an effective cache set to 60000 or above and for the lower values there was no improvement in performance over that already very fast plan. As said, it chooses sequential scans or "the wrong index plans" over a perfectly good plan that is just not selected when the parameters are "too well tuned" or sequential scanning of the table is allowed.

So I'm still looking for a way to get it to use the fast plan, instead of the slower ones that appear when I tend to tune the database...

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to