pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Mladen Gogala <mladen.gogala@vmsinfo.com>
To: Igor Neyman <ineyman@perceptron.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Wilson <david.t.wilson@gmail.com>
Cc: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Postgres 9.0 has a bias against indexes
Date: Thu, 27 Jan 2011 15:31:40 -0500
Message-ID: <4D41D62C.40501@vmsinfo.com> (raw )
In-Reply-To: <F4C27E77F7A33E4CA98C19A9DC6722A2070C2531@EXCHANGE.corp.perceptron.com >
References: <4D419214.8060003@vmsinfo.com >
<20110127154539.GE6475@aart.is.rice.edu >
<4D41959B.8020901@vmsinfo.com >
<AANLkTinjSQ4+56fZtDELXZ7uU49evc8U-Z0WV6U4FH6U@mail.gmail.com >
<17268.1296146447@sss.pgh.pa.us >
<4D41A496.70205@vmsinfo.com >
<F4C27E77F7A33E4CA98C19A9DC6722A2070C2531@EXCHANGE.corp.perceptron.com >
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
view thread (24+ messages) latest in thread
Message-ID: <4D41D62C.40501@vmsinfo.com>
Permalink: ../4D41D62C.40501@vmsinfo.com/
Also on: postgresql.org/message-id/4D41D62C.40501@vmsinfo.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mladen.gogala@vmsinfo.com, ineyman@perceptron.com, tgl@sss.pgh.pa.us, david.t.wilson@gmail.com, ktm@rice.edu
Subject: Re: Postgres 9.0 has a bias against indexes
In-Reply-To: <4D41D62C.40501@vmsinfo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox