From: Mark Kirkwood <markir@coretech.co.nz>
To: Litao Wu <litaowu@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres Optimizer is not smart enough?
Date: Thu, 13 Jan 2005 12:14:07 +1300
Message-ID: <41E5AF3F.2080100@coretech.co.nz> (raw)
In-Reply-To: <20050112222506.32084.qmail@web13124.mail.yahoo.com>
References: <20050112222506.32084.qmail@web13124.mail.yahoo.com>
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.
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: markir@coretech.co.nz, litaowu@yahoo.com
Subject: Re: Postgres Optimizer is not smart enough?
In-Reply-To: <41E5AF3F.2080100@coretech.co.nz>
* 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