pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mike Mascari <mascarm@mascari.com>
To: Litao Wu <litaowu@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres Optimizer is not smart enough?
Date: Wed, 12 Jan 2005 17:55:39 -0500
Message-ID: <41E5AAEB.2070706@mascari.com> (raw)
In-Reply-To: <20050112222506.32084.qmail@web13124.mail.yahoo.com>
References: <20050112222506.32084.qmail@web13124.mail.yahoo.com>

Litao Wu wrote:
> Hi All,
> 
> Here is my test comparison between Postgres (7.3.2)
> optimizer vs Oracle (10g) optimizer. 
> 
> It seems to me that Postgres optimizer is not smart 
> enough.
> 
> Did I miss anything?

Yeah, 7.4.

7.3.2 is *ancient*. Here's output from 7.4:

[test@ferrari] explain analyze
test-# SELECT module,  sum(action_deny)
test-# FROM test
test-# WHERE  created >= ('now'::timestamptz - '1
test'# day'::interval) AND customer_id='100'
test-#   AND  domain='100'
test-# GROUP BY module;
 
  QUERY PLAN 

-----------------------------------------------------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=5.69..5.69 rows=1 width=13) (actual 
time=715.058..715.060 rows=1 loops=1)
    ->  Index Scan using test_id1 on test  (cost=0.00..5.68 rows=1 
width=13) (actual time=0.688..690.459 rows=1 loops=1)
          Index Cond: ((customer_id = 100) AND (created >= '2005-01-11 
17:52:22.364145-05'::timestamp with time zone) AND (("domain")::text = 
'100'::text))
  Total runtime: 717.546 ms
(4 rows)

[test@ferrari] create index test_id2 on test(domain);
CREATE INDEX
[test@ferrari] analyze test;
ANALYZE
[test@ferrari]
[test@ferrari] explain analyze
test-# SELECT module,  sum(action_deny)
test-# FROM test
test-# WHERE  created >= ('now'::timestamptz - '1
test'# day'::interval) AND customer_id='100'
test-#   AND  domain='100'
test-# GROUP BY module;
                                                        QUERY PLAN 

------------------------------------------------------------------------------------------------------------------------
  HashAggregate  (cost=5.68..5.69 rows=1 width=13) (actual 
time=10.778..10.780 rows=1 loops=1)
    ->  Index Scan using test_id2 on test  (cost=0.00..5.68 rows=1 
width=13) (actual time=10.702..10.721 rows=1 loops=1)
          Index Cond: (("domain")::text = '100'::text)
          Filter: ((created >= '2005-01-11 
17:53:16.720749-05'::timestamp with time zone) AND (customer_id = 100))
  Total runtime: 11.039 ms
(5 rows)

[test@ferrari] select version();
  PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC 
i686-pc-linux-gnu-gcc (GCC) 3.4.0 20040204 (prerelease)
(1 row)

Hope that helps,

Mike Mascari



view thread (8+ messages)  latest in thread

Message-ID: <41E5AAEB.2070706@mascari.com>
Permalink:  ../41E5AAEB.2070706@mascari.com/
Also on:    postgresql.org/message-id/41E5AAEB.2070706@mascari.com

 · 

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: mascarm@mascari.com, litaowu@yahoo.com
  Subject: Re: Postgres Optimizer is not smart enough?
  In-Reply-To: <41E5AAEB.2070706@mascari.com>

* 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