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 630743A5E10 for ; Wed, 12 Jan 2005 23:10:27 +0000 (GMT) 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 93034-07 for ; Wed, 12 Jan 2005 23:10:13 +0000 (GMT) Received: from mail.coretech.co.nz (coretech.co.nz [202.36.204.41]) by svr1.postgresql.org (Postfix) with ESMTP id 3FB1E3A4652 for ; Wed, 12 Jan 2005 23:10:14 +0000 (GMT) Received: (qmail 442 invoked from network); 12 Jan 2005 23:10:11 -0000 Received: from 218-101-12-250.paradise.net.nz (HELO ?192.168.1.11?) (218.101.12.250) by 0 with SMTP; 12 Jan 2005 23:10:11 -0000 Message-ID: <41E5AF3F.2080100@coretech.co.nz> Date: Thu, 13 Jan 2005 12:14:07 +1300 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.7.2) Gecko/20041016 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Litao Wu Cc: pgsql-performance@postgresql.org Subject: Re: Postgres Optimizer is not smart enough? References: <20050112222506.32084.qmail@web13124.mail.yahoo.com> In-Reply-To: <20050112222506.32084.qmail@web13124.mail.yahoo.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/156 X-Sequence-Number: 9846 Litao Wu Wrote: > explain analyze > SELECT module, sum(action_deny) > FROM test > WHERE created >= ('now'::timestamptz - '1 > day'::interval) AND customer_id='100' > AND domain='100' > GROUP BY module; Here is my output for this query: QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=3.03..3.03 rows=1 width=13) (actual time=0.132..0.135 rows=1 loops=1) -> Index Scan using test_id2 on test (cost=0.00..3.02 rows=1 width=13) (actual time=0.085..0.096 rows=1 loops=1) Index Cond: (("domain")::text = '100'::text) Filter: ((created >= ('2005-01-13 11:57:34.673833+13'::timestamp with time zone - '1 day'::interval)) AND (customer_id = 100)) Total runtime: 0.337 ms (5 rows) Time: 8.424 ms The version is: PostgreSQL 8.0.0rc5 on i386-unknown-freebsd5.3, compiled by GCC gcc (GCC) 3.4.2 [FreeBSD] 20040728 I have random_page_cost = 0.8 in my postgresql.conf. Setting it back to the default (4) results in a plan using test_id1. A little experimentation showed that for my system random_page_cost=1 was where it changed from using test_id1 to test_id2. So changing this parameter may be helpful. I happen to have some debugging code enabled for the optimizer, and the issue appears to be that the costs of paths using these indexes are quite similar, so are quite sensitive to (some) parameter values. regards Mark P.s : 7.3.2 is quite old.