Re: Postgres 9.0 has a bias against indexes

On 1/27/2011 4:20 PM, Kenneth Marshall wrote:
> Interesting. Can you force it to use a Seqential Scan and if so, how
> does that affect the timing? i.e. Is the index scan actually faster?
>
> Cheers,
> Ken
Yes, Oracle can be forced into doing a sequential scan and it is
actually faster than an index scan:

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.18

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>
SQL> with e1(empno,ename,mgr,bossname,lev) as (
2 select /*+ full(emp) */ empno,ename,mgr,NULL,0 from emp where
empno=7839
3 union all
4 select /*+ full(e2) */
5 e2.empno,e2.ename,e2.mgr,e1.ename,e1.lev+1
6 from emp e2,e1
7 where e2.mgr=e1.empno)
8 select * from e1
9 /

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.14

Execution Plan
----------------------------------------------------------
Plan hash value: 2042363665

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

| Id | Operation | Name | Rows | Bytes
| Cost
(%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 15 | 795
| 10
(10)| 00:01:36 |

| 1 | VIEW | | 15 | 795
| 10
(10)| 00:01:36 |

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

|* 3 | TABLE ACCESS FULL | EMP | 1 | 24
| 3
(0)| 00:00:31 |

|* 4 | HASH JOIN | | 14 | 798
| 7
(15)| 00:01:06 |

| 5 | RECURSIVE WITH PUMP | | | |
| |

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

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

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

3 - filter("EMPNO"=7839)
4 - access("E2"."MGR"="E1"."EMPNO")

SQL> spool off

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