Postgres Optimizer is not smart enough?

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?

Thanks,

In Postgres:
============
drop table test;
create table test (
 module        character varying(50),
 action_deny   integer,
 created       timestamp with time zone,
 customer_id   integer,
 domain        character varying(255));
create or replace function insert_rows () returns
integer as '
BEGIN
   for i in 1 .. 500000 loop
     insert into test values (i, 2, now(), 100,  i);
   end loop;
   return 1;
END;
' LANGUAGE 'plpgsql';

select insert_rows();

create index test_id1 on test (customer_id, created,
domain);

analyze test;

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;


                          QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3.12..3.13 rows=1 width=9) (actual
time=91.05..91.05 rows=1 loops=1)
   ->  Group  (cost=3.12..3.12 rows=1 width=9) (actual
time=91.04..91.04 rows=1 loops=1)
         ->  Sort  (cost=3.12..3.12 rows=1 width=9)
(actual time=91.03..91.03 rows=1 loops=1)
               Sort Key: module
               ->  Index Scan using test_id1 on test
(cost=0.00..3.11 rows=1 width=9) (actual
time=0.03..91.00 rows=1 loops=1)
                     Index Cond: ((customer_id = 100)
AND (created >= '2005-01-11
14:48:44.832552-07'::timestamp with time zone) AND
("domain" = '100'::character varying))
 Total runtime: 91.13 msec
(7 rows)

create index test_id2 on test(domain);
analyze test;

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;


                          QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3.12..3.13 rows=1 width=9) (actual
time=90.30..90.30 rows=1 loops=1)
   ->  Group  (cost=3.12..3.12 rows=1 width=9) (actual
time=90.29..90.30 rows=1 loops=1)
         ->  Sort  (cost=3.12..3.12 rows=1 width=9)
(actual time=90.29..90.29 rows=1 loops=1)
               Sort Key: module
               ->  Index Scan using test_id1 on test
(cost=0.00..3.11 rows=1 width=9) (actual
time=0.03..90.25 rows=1 loops=1)
                     Index Cond: ((customer_id = 100)
AND (created >= '2005-01-11
14:51:09.555974-07'::timestamp with time zone) AND
("domain" = '100'::character varying))
 Total runtime: 90.38 msec
(7 rows)

WHY PG STILL CHOOSE INDEX test_id1???
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
BECAUSE QUERY WILL RUN MUCH FASTER USING test_id2!!!
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

drop index test_id1;
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;

   QUERY PLAN


-------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=3.12..3.13 rows=1 width=9) (actual
time=0.08..0.08 rows=1 loops=1)
   ->  Group  (cost=3.12..3.13 rows=1 width=9) (actual
time=0.08..0.08 rows=1 loops=1)
         ->  Sort  (cost=3.12..3.13 rows=1 width=9)
(actual time=0.07..0.07 rows=1 loops=1)
               Sort Key: module
               ->  Index Scan using test_id2 on test
(cost=0.00..3.11 rows=1 width=9) (actual
time=0.04..0.05 rows=1 loops=1)
                     Index Cond: ("domain" =
'100'::character varying)
                     Filter: ((created >= '2005-01-11
14:53:58.806364-07'::timestamp with time zone) AND
(customer_id = 100))
 Total runtime: 0.14 msec
(8 rows)

In Oracle:
==========
drop table test;
create table test (
 module        character varying(50),
 action_deny   integer,
 created       timestamp with time zone,
 customer_id   integer,
 domain        character varying(255));

begin
   for i in 1..500000 loop
     insert into test values (i, 2, current_timestamp,
100, i);
   end loop;
end;
/

create index test_id1 on test (customer_id, created,
domain);

analyze table test compute statistics;

set autot on
set timing on

SELECT module,  sum(action_deny)
FROM test
WHERE  created >= (current_timestamp - interval '1'
day) AND customer_id=100
  AND  domain='100'
GROUP BY module
/

MODULE
SUM(ACTION_DENY)
--------------------------------------------------
----------------
100
           2

Elapsed: 00:00:00.67

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25
Card=1 Bytes=29
          )

   1    0   SORT (GROUP BY) (Cost=25 Card=1 Bytes=29)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
(TABLE) (Cost=24
           Card=1 Bytes=29)

   3    2       INDEX (RANGE SCAN) OF 'TEST_ID1'
(INDEX) (Cost=23 Card
          =4500)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2292  consistent gets
       2291  physical reads
          0  redo size
        461  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

create index test_id2 on test (domain);

SELECT module,  sum(action_deny)
FROM test
WHERE  created >= (current_timestamp - interval '1'
day) AND customer_id=100
  AND  domain='100'
GROUP BY module
/

MODULE
SUM(ACTION_DENY)
--------------------------------------------------
----------------
100
           2

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5
Card=1 Bytes=29)
   1    0   SORT (GROUP BY) (Cost=5 Card=1 Bytes=29)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
(TABLE) (Cost=4
          Card=1 Bytes=29)

   3    2       INDEX (RANGE SCAN) OF 'TEST_ID2'
(INDEX) (Cost=3 Card=
          1)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        461  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed





__________________________________
Do you Yahoo!?
All your favorites on one personal page � Try My Yahoo!
http://my.yahoo.com