From: Guillaume Smet <guillaume.smet@gmail.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: pgsql-performance@postgresql.org
Subject: Re: planner with index scan cost way off actual cost, advices to tweak cost constants?
Date: Sat, 18 Mar 2006 11:20:45 +0100
Message-ID: <1d4e0c10603180220se128a79r71534db9349bd746@mail.gmail.com> (raw)
In-Reply-To: <87ek11gz69.fsf@meuh.mnc.lan>
References: <87ek11gz69.fsf@meuh.mnc.lan>
Guillaume,
On 17 Mar 2006 11:09:50 +0100, Guillaume Cottenceau
wrote:
> Reading the documentation and postgresql list archives, I have
> run ANALYZE right before my tests, I have increased the
> statistics target to 50 for the considered table; my problem is
> that the index scan cost reported by EXPLAIN seems to be around
> 12.7 times higher that it should, a figure I suppose incompatible
> (too large) for just random_page_cost and effective_cache_size
> tweaks.
It's not surprising you have a high cost for an index scan which is
planned to return and returns so much rows. I really don't think the
planner does something wrong on this one.
AFAIK, increasing the statistics target won't do anything to reduce
the cost as the planner estimation for the number of returned rows is
already really accurate and probably can't be better.
> Of course real queries use smaller date ranges.
What about providing us the respective plans for your real queries?
And in a real case. It's a bad idea to compare index scan and seqscan
when your data have to be loaded in RAM.
Before doing so create an index on the date column to have the most
effective index possible.
> - I then tried to tweak random_page_cost and effective_cache_size
> following advices from documentation:
>
> SET random_page_cost = 2;
random_page_cost is the way to go for this sort of thing but I don't
think it's a good idea to have it too low globally and I'm still
thinking the problem is that your test case is not accurate.
--
Guillaume
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: guillaume.smet@gmail.com, gc@mnc.ch
Subject: Re: planner with index scan cost way off actual cost, advices to tweak cost constants?
In-Reply-To: <1d4e0c10603180220se128a79r71534db9349bd746@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox