Re: [HACKERS] Please Help: PostgreSQL Query Optimizer

[Josh Berkus <josh(at)agliodbs(dot)com>]

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