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

Author

Re: [HACKERS] Please Help: PostgreSQL Query Optimizer

Josh Berkus

2005-12-11, 8:24 pm

Anjan,

> In our case we are reading pages from Main Memory File System, but not from
> Disk. Will it be sufficient, if we change the default values of above
> paramters in "src/include/optimizer/cost.h and
> src/backend/utils/misc/postgresql.conf.sample" as follows:
>
> random_page_cost = 4;

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.

> cpu_tuple_cost = 2;
> cpu_index_tuple_cost
= 0.2;
> cpu_operator_cost = 0.05;

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!

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Tom Lane

2005-12-11, 8:24 pm

Josh Berkus <josh@agliodbs.com> writes:
> I don't see why you're increasing the various cpu_* costs.

You missed the point Josh --- these numbers are relative to the cost of
a page fetch, so if page fetch is measured in microseconds instead of
milliseconds, then you *do* want to bump the CPU costs up.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links