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
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