Thread: Relation of cpu_*_costs?

Dear Gurus,

Please feel free to show me to the archives if my question has already been
answered.

Last week I fumbled with CPU_TUPLE_COST and revealed that 4 out of 4 tested
queries improved by 10-60% if I changed it from 0.01 (default) to 0.40
(ugh). Setting it higher did not bring any improvement.

%----------------------- cut here -----------------------%
QUESTION1: is there a (theoretical or practical) relation between this one
and the other cpu costs? Should I also increase those values by the same
rate and find a balance that way?

As far as I can guess, there should be a linear relation, i.e.
cpu_tuple_cost:cpu_index_tuple_cost:cpu_operator_cost should be a constant
ratio, but then again, I suspect there is a cause that they have separate
entries in the config file ;)


%----------------------- cut here -----------------------%
The queries were, or contained, something like:

  SELECT s.qty FROM a, s WHERE a.id = s.a_id AND a.b_id = 1234;

where
* "a" and "s" are in 1:N relation,
* "b" and "a" are in 1:N relation,
* a.id is pkey in "a" and b.id is pkey in "b".

These queries usually return up to 6-10% of the tuples in s (about 16k of
220k) and the planner chose seq scans on s. Disabling seq scan and some
other things finally brought up a plan containing index scans that improved
two queries. (I tested the other two after I found out the solution of
these, to see if they improve or get worse)

Also noticed that the largest gain was from the smallest change on
cpu_tuple_cost: the query with the largest improvement (to 32% of orig time)
chose the better plan from 0.03, but the other one improved (to 79%) only if
set cpu_tuple_cost to 0.40 or higher.


%----------------------- cut here -----------------------%
QUESTION2: am I right setting cpu_tuple_cost, or may there be another cause
of poor plan selection? Also tried lowering random_page_cost, but even 1.0
didn't yield any improvement.


%----------------------- cut here -----------------------%
CONFIGURATION: PostgreSQL 7.3.4, IBM Xeon 2x2.4GHz HT, 5x36GB 10krpm HW
RAID-5.

We found out quite early that random page cost is quite low (now we have it
at 1.5-- maybe it's still to high) and it's true that tasks that require raw
cpu power aren't very much faster than PIII-800. Unfortunately I can't test
the same hw on 7.4 yet, since it's a production server.

TIA,
G.
%----------------------- cut here -----------------------%
\end

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu> writes:
> Last week I fumbled with CPU_TUPLE_COST and revealed that 4 out of 4 tested
> queries improved by 10-60% if I changed it from 0.01 (default) to 0.40
> (ugh). Setting it higher did not bring any improvement.

That's pretty hard to believe; particularly on modern machines, I'd
think that moving it down would make more sense than moving it up.
You're essentially asserting that the CPU time to process one tuple
is almost half of the time needed to bring a page in from disk.

I suspect that your test cases were toy cases small enough to be
fully cached and thus not incur any actual I/O ...

> [ trying to get a nestloop indexscan plan to be generated ]

I believe that the planner's cost model for nestloops with inner
indexscan is wrong: it costs each inner iteration independently, when
in fact there should be some savings, because at least the topmost
levels of the index will soon be fully cached.  However, when I tried
to work out a proper model of this effect, I ended up with equations
that gave higher indexscan costs than what's in there now :-(.  So that
didn't seem like it would make anyone happy.

            regards, tom lane

Dear Tom,

Thanks for your response.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
Sent: Monday, June 07, 2004 3:51 PM


> That's pretty hard to believe; particularly on modern machines, I'd
> think that moving it down would make more sense than moving it up.
> You're essentially asserting that the CPU time to process one tuple
> is almost half of the time needed to bring a page in from disk.

That is exactly what I had in mind. We found that 5x10krpm HW RAID 5 array
blazing fast, while we were really disappointed about CPU. E.g.
* tar'ing 600MB took seconds; gzip'ing it took minutes.
* initdb ran so fast that I didn't have time to hit Ctrl+C because
  I forgot a switch ;)
* dumping the DB in or out was far faster than adddepend between 7.2 and 7.3
* iirc index scans returning ~26k rows of ~64k were faster than seq scan.
  (most suspicious case of disk cache)

But whatever is the case with my hardware -- could you tell me something
(even a search keyword ;) ) about my theoretical question: i.e. relation of
cpu_*_costs?

> I suspect that your test cases were toy cases small enough to be
> fully cached and thus not incur any actual I/O ...

Dunno. The server has 1GB RAM; full DB is ~100MB; largest query was ~7k
which moved at least 2 tables of >200k rows and several smaller ones. If it
is a "toy case" for such hw, I humbly accept your opinion.

BTW its runtime improved from 53 to 48 sec -- all due to changing cpu tuple
cost. I ran the query at different costs, in fast succession:

run    cost    sec
 #1    0.01    53
 #2    0.4     50
 #3    1.0     48
 #4    1.0     48
 #5    0.4     48
 #6    0.01    53

For the second result, I'd say disk cache, yes-- but what about the last
result? It's all the same as the first one. Must have been some plan change
(I can send the exp-ana results if you wish)

G.
%----------------------- cut here -----------------------%
\end