The Two Towers

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: The Two Towers
Date: 2010-06-08 02:41:54
Message-ID: 4C0DADF2.9000305@vmsinfo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
<div class="moz-text-html" lang="x-western">
Battleground was a machine running CentOS 5.5 and the two towers were 2
databases. First, PostgreSQL 8.4.4:<br>
<br>
<blockquote>s<tt><font color="#3333ff">cott=#&nbsp; select count(*) from emp
e1,emp e2, emp e3, emp e4,emp e5, emp e6;</font></tt><br>
<tt><font color="#3333ff">&nbsp; count&nbsp; </font></tt><br>
<tt><font color="#3333ff">---------</font></tt><br>
<tt><font color="#3333ff">&nbsp;7529536</font></tt><br>
<tt><font color="#3333ff">(1 row)</font></tt><br>
<br>
<tt><font color="#3333ff">Time: 2127.345 ms</font></tt><br>
<tt><font color="#3333ff">scott=# select version();</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
version&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></tt><br>
<tt><font color="#3333ff">--------------------------------------------------------------------------------</font></tt><br>
<tt><font color="#3333ff">--------------------------------</font></tt><br>
<tt><font color="#3333ff">&nbsp;PostgreSQL 8.4.4 on i686-redhat-linux-gnu,
compiled by GCC gcc (GCC) 4.1.2 2008</font></tt><br>
<tt><font color="#3333ff">0704 (Red Hat 4.1.2-46), 32-bit</font></tt><br>
<tt><font color="#3333ff">(1 row)</font></tt><br>
<br>
<tt><font color="#3333ff">Time: 1.008 ms</font></tt><br>
<tt><font color="#3333ff">scott=# explain analyze </font></tt><br>
<tt><font color="#3333ff">scott-# select count(*) from emp e1,emp e2,
emp e3, emp e4,emp e5, emp e6;</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
QUERY PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</font></tt><br>
<tt><font color="#3333ff">--------------------------------------------------------------------------------</font></tt><br>
<tt><font color="#3333ff">---------------------------------------------------------</font></tt><br>
<tt><font color="#3333ff">&nbsp;Aggregate&nbsp;
(cost=1052615959933232.62..1052615959933232.62 rows=1 width=0) (actu</font></tt><br>
<tt><font color="#3333ff">al time=13684.811..13684.811 rows=1 loops=1)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp; -&gt;&nbsp; Nested Loop&nbsp;
(cost=16.60..935975959933232.62 rows=46656000000000000 width</font></tt><br>
<tt><font color="#3333ff">=0) (actual time=0.038..10508.108
rows=7529536 loops=1)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Nested Loop&nbsp;
(cost=0.00..2855959933216.00 rows=77760000000000 width</font></tt><br>
<tt><font color="#3333ff">=0) (actual time=0.025..820.829 rows=537824
loops=1)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Nested Loop&nbsp;
(cost=0.00..4759933216.00 rows=129600000000 widt</font></tt><br>
<tt><font color="#3333ff">h=0) (actual time=0.021..59.701 rows=38416
loops=1)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Nested Loop&nbsp;
(cost=0.00..7933216.00 rows=216000000 widt</font></tt><br>
<tt><font color="#3333ff">h=0) (actual time=0.018..4.424 rows=2744
loops=1)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Nested
Loop&nbsp; (cost=0.00..13216.00 rows=360000 wid</font></tt><br>
<tt><font color="#3333ff">th=0) (actual time=0.015..0.333 rows=196
loops=1)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq
Scan on emp e1&nbsp; (cost=0.00..16.00 rows=</font></tt><br>
<tt><font color="#3333ff">600 width=0) (actual time=0.009..0.018
rows=14 loops=1)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq
Scan on emp e6&nbsp; (cost=0.00..16.00 rows=</font></tt><br>
<tt><font color="#3333ff">600 width=0) (actual time=0.002..0.010
rows=14 loops=14)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan
on emp e5&nbsp; (cost=0.00..16.00 rows=600 wi</font></tt><br>
<tt><font color="#3333ff">dth=0) (actual time=0.002..0.008 rows=14
loops=196)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on emp
e4&nbsp; (cost=0.00..16.00 rows=600 width=0)</font></tt><br>
<tt><font color="#3333ff">&nbsp;(actual time=0.001..0.007 rows=14
loops=2744)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on emp e3&nbsp;
(cost=0.00..16.00 rows=600 width=0) (actu</font></tt><br>
<tt><font color="#3333ff">al time=0.001..0.007 rows=14 loops=38416)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Materialize&nbsp;
(cost=16.60..22.60 rows=600 width=0) (actual time=0.00</font></tt><br>
<tt><font color="#3333ff">0..0.006 rows=14 loops=537824)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on emp e2&nbsp;
(cost=0.00..16.00 rows=600 width=0) (actu</font></tt><br>
<tt><font color="#3333ff">al time=0.003..0.012 rows=14 loops=1)</font></tt><br>
<tt><font color="#3333ff">&nbsp;Total runtime: 13684.977 ms</font></tt><br>
<tt><font color="#3333ff">(14 rows)</font></tt><br>
<tt><font color="#3333ff">Time: 13714.576 ms</font></tt><br>
<tt><font color="#3333ff">scott=# \d+ emp</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Table
"public.emp"</font></tt><br>
<tt><font color="#3333ff">&nbsp; Column&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
Modifiers | Storage&nbsp; | Description </font></tt><br>
<tt><font color="#3333ff">----------+-----------------------------+-----------+----------+-------------</font></tt><br>
<tt><font color="#3333ff">&nbsp;empno&nbsp;&nbsp;&nbsp; | smallint&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
not null&nbsp; | plain&nbsp;&nbsp;&nbsp; | </font></tt><br>
<tt><font color="#3333ff">&nbsp;ename&nbsp;&nbsp;&nbsp; | character varying(10)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
not null&nbsp; | extended | </font></tt><br>
<tt><font color="#3333ff">&nbsp;job&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | character varying(9)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | extended | </font></tt><br>
<tt><font color="#3333ff">&nbsp;mgr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | smallint&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | plain&nbsp;&nbsp;&nbsp; | </font></tt><br>
<tt><font color="#3333ff">&nbsp;hiredate | timestamp without time zone
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | plain&nbsp;&nbsp;&nbsp; | </font></tt><br>
<tt><font color="#3333ff">&nbsp;sal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | double precision&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | plain&nbsp;&nbsp;&nbsp; | </font></tt><br>
<tt><font color="#3333ff">&nbsp;comm&nbsp;&nbsp;&nbsp;&nbsp; | double precision&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | plain&nbsp;&nbsp;&nbsp; | </font></tt><br>
<tt><font color="#3333ff">&nbsp;deptno&nbsp;&nbsp; | smallint&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | plain&nbsp;&nbsp;&nbsp; | </font></tt><br>
<tt><font color="#3333ff">Indexes:</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp; "emp_pkey" PRIMARY KEY, btree (empno)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp; "emp_deptno_i" btree (deptno)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp; "emp_ename_id" btree (ename)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp; "ind_emp_deptno" btree (deptno)</font></tt><br>
<tt><font color="#3333ff">Foreign-key constraints:</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp; "fk_deptno" FOREIGN KEY (deptno)
REFERENCES dept(deptno)</font></tt><br>
<tt><font color="#3333ff">Has OIDs: no</font></tt><br>
</blockquote>
<br>
Now, Oracle 11.2.0.1 with the April patch:<br>
<blockquote><tt><font color="#3333ff">SQL&gt; set autotrace on</font></tt><br>
<tt><font color="#3333ff">SQL&gt; select count(*) from emp e1,emp e2,
emp e3, emp e4,emp e5, emp e6;</font></tt><br>
<br>
<tt><font color="#3333ff">&nbsp; COUNT(*)</font></tt><br>
<tt><font color="#3333ff">----------</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp; 7529536</font></tt><br>
<br>
<tt><font color="#3333ff">Elapsed: 00:00:00.85</font></tt><br>
<br>
<tt><font color="#3333ff">Execution Plan</font></tt><br>
<tt><font color="#3333ff">----------------------------------------------------------</font></tt><br>
<tt><font color="#3333ff">Plan hash value: 1049923164</font></tt><br>
<br>
<tt><font color="#3333ff">------------------------------------------------------------------------------</font></tt><br>
<tt><font color="#3333ff">| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
Name&nbsp;&nbsp; | Rows&nbsp; | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |</font></tt><br>
<tt><font color="#3333ff">------------------------------------------------------------------------------</font></tt><br>
<tt><font color="#3333ff">|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp; 157K&nbsp; (1)| 00:31:25 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp;&nbsp; 1 |&nbsp; SORT AGGREGATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</font></tt><br>
<tt><font color="#3333ff">|&nbsp;&nbsp; 2 |&nbsp;&nbsp; MERGE JOIN CARTESIAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 7529K|&nbsp;&nbsp; 157K&nbsp; (1)| 00:31:25 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; MERGE JOIN CARTESIAN&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 537K| 11224&nbsp;&nbsp; (1)| 00:02:15 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp;&nbsp; 4 |&nbsp;&nbsp;&nbsp;&nbsp; MERGE JOIN CARTESIAN&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 38416 |&nbsp;&nbsp; 808&nbsp;&nbsp; (1)| 00:00:10 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp;&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MERGE JOIN CARTESIAN&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2744 |&nbsp;&nbsp;&nbsp; 62&nbsp;&nbsp; (0)| 00:00:01 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp;&nbsp; 6 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MERGE JOIN CARTESIAN&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp; 196 |&nbsp;&nbsp;&nbsp;&nbsp; 7&nbsp;&nbsp; (0)| 00:00:01 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp;&nbsp; 7 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INDEX FULL SCAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
PK_EMP |&nbsp;&nbsp;&nbsp; 14 |&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp; (0)| 00:00:01 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp;&nbsp; 8 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BUFFER SORT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 14 |&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp; (0)| 00:00:01 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp;&nbsp; 9 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN|
PK_EMP |&nbsp;&nbsp;&nbsp; 14 |&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; (0)| 00:00:01 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp; 10 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BUFFER SORT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 14 |&nbsp;&nbsp;&nbsp; 62&nbsp;&nbsp; (0)| 00:00:01 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp; 11 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN |
PK_EMP |&nbsp;&nbsp;&nbsp; 14 |&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; (0)| 00:00:01 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp; 12 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BUFFER SORT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 14 |&nbsp;&nbsp; 808&nbsp;&nbsp; (1)| 00:00:10 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp; 13 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN&nbsp; |
PK_EMP |&nbsp;&nbsp;&nbsp; 14 |&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; (0)| 00:00:01 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp; 14 |&nbsp;&nbsp;&nbsp;&nbsp; BUFFER SORT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 14 | 11224&nbsp;&nbsp; (1)| 00:02:15 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp; 15 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN&nbsp;&nbsp; |
PK_EMP |&nbsp;&nbsp;&nbsp; 14 |&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; (0)| 00:00:01 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp; 16 |&nbsp;&nbsp;&nbsp; BUFFER SORT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp; 14 |&nbsp;&nbsp; 157K&nbsp; (1)| 00:31:25 |</font></tt><br>
<tt><font color="#3333ff">|&nbsp; 17 |&nbsp;&nbsp;&nbsp;&nbsp; INDEX FAST FULL SCAN&nbsp;&nbsp;&nbsp; |
PK_EMP |&nbsp;&nbsp;&nbsp; 14 |&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp; (0)| 00:00:01 |</font></tt><br>
<tt><font color="#3333ff">------------------------------------------------------------------------------</font></tt><br>
<br>
<br>
<tt><font color="#3333ff">Statistics</font></tt><br>
<tt><font color="#3333ff">----------------------------------------------------------</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; recursive calls</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; db block gets</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21&nbsp; consistent gets</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; physical reads</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; redo size</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 425&nbsp; bytes sent via SQL*Net to
client</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 419&nbsp; bytes received via SQL*Net
from client</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp; SQL*Net roundtrips to/from
client</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp; sorts (memory)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp; sorts (disk)</font></tt><br>
<tt><font color="#3333ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp; rows processed</font></tt><br>
<br>
<tt><font color="#3333ff">SQL&gt; </font></tt><br>
<br>
<tt><font color="#3333ff">SQL&gt; select * from v$version;</font></tt><br>
<br>
<tt><font color="#3333ff">BANNER</font></tt><br>
<tt><font color="#3333ff">--------------------------------------------------------------------------------</font></tt><br>
<tt><font color="#3333ff">Oracle Database 11g Enterprise Edition
Release 11.2.0.1.0 - Production</font></tt><br>
<tt><font color="#3333ff">PL/SQL Release 11.2.0.1.0 - Production</font></tt><br>
<tt><font color="#3333ff">CORE&nbsp;&nbsp;&nbsp; 11.2.0.1.0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Production</font></tt><br>
<tt><font color="#3333ff">TNS for Linux: Version 11.2.0.1.0 -
Production</font></tt><br>
<tt><font color="#3333ff">NLSRTL Version 11.2.0.1.0 - Production</font></tt><br>
</blockquote>
<br>
Both databases have current statistics, the data is exactly the same,
as shown by the results. Oracle finished in 0.85 second (850
milliseconds) while PostgreSQL took 2127.345 ms, 2.5 times slower than
Oracle. The difference that is obvious is the access path: Postgres
chose nested loops, while Oracle chose merge join and utilized the
primary key for the table. Postgres, apparently cannot do "fast full
scan" of an index and doesn't know how to utilize the primary key in
cases like this. However, my gripe is that the optimizer should have
selected merge join, just like Oracle did. In this case, nested loops
are definitely the wrong choice. Rule based optimizers, the kind of the
optimizer that takes into consideration only the structure of the
table, usually ends up being dominated by the nested loops method.
Nested loops method usually dominates the OLTP type applications but
can really mess up large reports. I am under the impression that
Postgres query planner is geared toward the OLTP type of the database.
Maybe a new parameter is needed that would somehow shift gears to "data
warehouse use", on demand? I have to say, the advantage is still on the
side of Sauron.<br>
<br>
<br>
<pre class="moz-signature" cols="72">--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
<a class="moz-txt-link-abbreviated" href="http://www.vmsinfo.com">www.vmsinfo.com</a>
</pre>
</div>
<pre class="moz-signature" cols="72">--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
<a class="moz-txt-link-abbreviated" href="http://www.vmsinfo.com">www.vmsinfo.com</a>
</pre>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 20.2 KB

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Donn Washburn 2010-06-08 03:30:27 Re: The Two Towers
Previous Message Mladen Gogala 2010-06-08 00:51:44 Re: The escape clause in the SELECT statement in PostgreSQL 8.4