From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Litao Wu <litaowu(at)yahoo(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Postgres Optimizer is not smart enough? |
Date: | 2005-01-12 22:55:39 |
Message-ID: | 41E5AAEB.2070706@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)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(at)ferrari] create index test_id2 on test(domain);
CREATE INDEX
[test(at)ferrari] analyze test;
ANALYZE
[test(at)ferrari]
[test(at)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(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Kirkwood | 2005-01-12 23:14:07 | Re: Postgres Optimizer is not smart enough? |
Previous Message | Litao Wu | 2005-01-12 22:25:06 | Postgres Optimizer is not smart enough? |