pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: SZŰCS Gábor <surrano@mailbox.hu>
To: pgsql-performance@postgresql.org
Subject: Relation of cpu_*_costs?
Date: Mon, 7 Jun 2004 09:36:19 +0200
Message-ID: <076f01c44c62$208aa680$0403a8c0@fejleszt4> (raw)

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




view thread (3+ messages)  latest in thread

Message-ID: <076f01c44c62$208aa680$0403a8c0@fejleszt4>
Permalink:  ../076f01c44c62$208aa680$0403a8c0@fejleszt4/
Also on:    postgresql.org/message-id/076f01c44c62$208aa680$0403a8c0@fejleszt4

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: surrano@mailbox.hu
  Subject: Re: Relation of cpu_*_costs?
  In-Reply-To: <076f01c44c62$208aa680$0403a8c0@fejleszt4>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox