X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0C2DFD1B17C for ; Mon, 7 Jun 2004 04:35:49 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15003-04 for ; Mon, 7 Jun 2004 07:35:46 +0000 (GMT) Received: from fe07.axelero.hu (fe07.axelero.hu [195.228.240.95]) by svr1.postgresql.org (Postfix) with ESMTP id E9E15D1B178 for ; Mon, 7 Jun 2004 04:35:42 -0300 (ADT) Received: from fe07 (localhost-02 [127.0.2.1]) by fe07.axelero.hu (8.12.11/8.12.11) with SMTP id i577Zhwr086237 for ; Mon, 7 Jun 2004 09:35:43 +0200 (CEST) Received: from fe07.axelero.hu [127.0.2.1] via SMTP gateway by fe07 [195.228.240.95]; id A050CD2D753 at Mon, 07 Jun 2004 09:35:43 +0200 Received: from fejleszt4 (121-248-182-81.adsl-fixip.axelero.hu [81.182.248.121]) by fe07.axelero.hu (8.12.11/8.12.11) with SMTP id i577ZcGD085926 for ; Mon, 7 Jun 2004 09:35:38 +0200 (CEST) Message-ID: <076f01c44c62$208aa680$0403a8c0@fejleszt4> From: "=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" To: Subject: Relation of cpu_*_costs? Date: Mon, 7 Jun 2004 09:36:19 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-RAVMilter-Version: 8.4.3(snapshot 20030217) (fe07.axelero.hu) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/55 X-Sequence-Number: 7113 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