Re: Please Help: PostgreSQL Query Optimizer

Anjan,

> But, in PostgreSQL  all costs are  scaled relative to a page fetch. If we
> make both sequential_page_fetch_cost and random_page_cost to "1", then  we
> need to increase the various cpu_* paramters by multiplying the default
> values with appropriate  Scaling Factor.  Now, we need to determine this
> Scaling Factor.

I see, so you're saying that because the real cost of a page fetch has
decreased, the CPU_* costs should increase proportionally because relative to
the real costs of a page fetch they should be higher?  That makes a sort of
sense.

The problem that you're going to run into is that currently we have no
particularly reason to believe that the various cpu_* costs are more than
very approximately correct as rules of thumb.  So I think you'd be a lot
better off trying to come up with some means of computing the real cpu costs
of each operation, rather than trying to calculate a multiple of numbers
which may be wrong in the first place.

I know that someone on this list was working on a tool to digest EXPLAIN
ANALYZE results and run statistics on them.   Can't remember who, though.

Also, I'm still curious on how you're handling shared_mem, work_mem and
maintenance_mem.  You didn't answer last time.

--
Josh Berkus
Aglio Database Solutions
San Francisco