PostgreSQL Documentation - Re: [HACKERS] Please Help: PostgreSQL Query Optimizer

Author

Re: [HACKERS] Please Help: PostgreSQL Query Optimizer

Anjan Kumar. A.

2005-12-11, 8:24 pm

Since sequential access is not significantly faster than random access in a MMDB, random_page_cost will be approximately same as sequential page fetch cost.

As every thing is present in Main Memory, we need to give approximately same cost to read/write to Main Memory and CPU Related operations.

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

Still, i want to confirm whether this approach is the correct one.

On Sun, 11 Dec 2005, Josh Berkus wrote:

> Anjan,
>
>
> This should be dramatically lowered. It's supposed to represent the ratio of
> seek-fetches to seq scans on disk. Since there's no disk, it should be a
> flat 1.0. However, we are aware that there are flaws in our calculations
> involving random_page_cost, such that the actual number for a system where
> there is no disk cost would be lower than 1.0. Your research will hopefully
> help us find these flaws.
>
>
> I don't see why you're increasing the various cpu_* costs. CPU costs would be
> unaffected by the database being in memory. In general, I lower these by a
> divisor based on the cpu speed; for example, on a dual-opteron system I lower
> the defaults by /6. However, that's completely unrelated to using an MMDB.
>
> So, other than random_page_cost, I don't know of other existing GUCs that
> would be directly related to using a disk/not using a disk. How are you
> handling shared memory and work memory?
>
> I look forward to hearing more about your test!
>
>

--
Regards.

Anjan Kumar A.
MTech2, Comp Sci.,
www.cse.iitb.ac.in/~anjankumar
____________________
____________________
____________________
__
Do not handicap your children by making their lives easy.
-- Robert Heinlein

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Josh Berkus

2005-12-13, 3:23 am

Anjan,

> But, in PostgreSQL all costs are scaled relative to a page fetch. If we
> make both sequential_page_fetc
h_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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links