pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.




view thread (8+ messages)  latest in thread

Message-ID: <41E5AF3F.2080100@coretech.co.nz>
Permalink:  ../41E5AF3F.2080100@coretech.co.nz/
Also on:    postgresql.org/message-id/41E5AF3F.2080100@coretech.co.nz

 · 

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: 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