Re: Postgres 9.0 has a bias against indexes

On 1/27/2011 3:10 PM, Igor Neyman wrote:
>
> Mladen,
>
> I don't think, this is exclusive Postgres feature.
> I'm pretty sure, Oracle optimizer will do "TABLE ACCESS (FULL)" instead
> of using index on 14-row table either.
>
> Regards,
> Igor Neyman

Well, lets' see:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0    Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

Elapsed: 00:00:00.00
SQL> set autotrace on explain
SQL> with e(empno,ename,mgr,bossname,lev) as (
   2  select empno,ename,mgr,NULL,0 from emp where empno=7839
   3  union all
   4  select emp.empno,emp.ename,emp.mgr,e.ename,e.lev+1
   5  from emp,e
   6  where emp.mgr=e.empno)
   7  select * from e
   8  /

      EMPNO ENAME             MGR BOSSNAME          LEV
---------- ---------- ---------- ---------- ----------
       7839 KING                                      0
       7566 JONES            7839 KING                1
       7698 BLAKE            7839 KING                1
       7782 CLARK            7839 KING                1
       7499 ALLEN            7698 BLAKE               2
       7521 WARD             7698 BLAKE               2
       7654 MARTIN           7698 BLAKE               2
       7788 SCOTT            7566 JONES               2
       7844 TURNER           7698 BLAKE               2
       7900 JAMES            7698 BLAKE               2
       7902 FORD             7566 JONES               2

      EMPNO ENAME             MGR BOSSNAME          LEV
---------- ---------- ---------- ---------- ----------
       7934 MILLER           7782 CLARK               2
       7369 SMITH            7902 FORD                3
       7876 ADAMS            7788 SCOTT               3

14 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2925328376

--------------------------------------------------------------------------------
--------------------

| Id  | Operation                                 | Name   | Rows  |
Bytes | Cos
t (%CPU)| Time     |

--------------------------------------------------------------------------------
--------------------

|   0 | SELECT STATEMENT                          |        |    15 |   795 |
  6  (17)| 00:00:56 |

|   1 |  VIEW                                     |        |    15 |   795 |
  6  (17)| 00:00:56 |

|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|        |       |       |
         |          |

|   3 |    TABLE ACCESS BY INDEX ROWID            | EMP    |     1 |    24 |
  1   (0)| 00:00:11 |

|*  4 |     INDEX UNIQUE SCAN                     | PK_EMP |     1 |       |
  0   (0)| 00:00:01 |

|*  5 |    HASH JOIN                              |        |    14 |   798 |
  5  (20)| 00:00:46 |

|   6 |     RECURSIVE WITH PUMP                   |        |       |       |
         |          |

|   7 |     TABLE ACCESS FULL                     | EMP    |    14 |   336 |
  3   (0)| 00:00:31 |

--------------------------------------------------------------------------------
--------------------


Predicate Information (identified by operation id):
---------------------------------------------------

    4 - access("EMPNO"=7839)
    5 - access("EMP"."MGR"="E"."EMPNO")

Note
-----
    - SQL plan baseline "SQL_PLAN_1tmxjj25531vff51d791e" used for this
statement

SQL> spool off


There is INDEX UNIQUE SCAN PK_EMP.  Oracle will use an index.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com