Skip site navigation (1) Skip section navigation (2)

Re: Postgres 9.0 has a bias against indexes

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Kenneth Marshall <ktm(at)rice(dot)edu>
Cc: Igor Neyman <ineyman(at)perceptron(dot)com>, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, David Wilson <david(dot)t(dot)wilson(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Postgres 9.0 has a bias against indexes
Date: 2011-01-27 21:31:24
Message-ID: 4D41E42C.2090702@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Igor NeymanDate: 2011-01-27 21:32:02
Subject: Re: Postgres 9.0 has a bias against indexes
Previous:From: Scott MarloweDate: 2011-01-27 21:25:04
Subject: Re: Postgres 9.0 has a bias against indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group