From: Mladen Gogala <mladen.gogala@vmsinfo.com>
To: 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 10:56:11 -0500
Message-ID: <4D41959B.8020901@vmsinfo.com> (raw)
In-Reply-To: <20110127154539.GE6475@aart.is.rice.edu>
References: <4D419214.8060003@vmsinfo.com>
<20110127154539.GE6475@aart.is.rice.edu>
On 1/27/2011 10:45 AM, Kenneth Marshall wrote:
> PostgreSQL will only use an index if the planner thinks that it
> will be faster than the alternative, a sequential scan in this case.
> For 14 rows, a sequential scan is 1 read and should actually be
> faster than the index. Did you try the query using EXPLAIN ANALYZE
> once with index and once without? What were the timings? If they
> do not match reality, adjusting cost parameters would be in order.
>
I did. I even tried with an almost equivalent outer join:
explain analyze select e1.empno,e1.ename,e2.empno,e2.ename
from emp e1 left outer join emp e2 on (e1.mgr=e2.empno);
QUERY PLAN
--------------------------------------------------------------------------------
------------------------------
Nested Loop Left Join (cost=0.00..7.25 rows=14 width=16) (actual
time=0.028..0
.105 rows=14 loops=1)
Join Filter: (e1.mgr = e2.empno)
-> Seq Scan on emp e1 (cost=0.00..2.14 rows=14 width=10) (actual
time=0.006
..0.010 rows=14 loops=1)
-> Materialize (cost=0.00..2.21 rows=14 width=8) (actual
time=0.001..0.003
rows=14 loops=14)
-> Seq Scan on emp e2 (cost=0.00..2.14 rows=14 width=8)
(actual time=
0.001..0.005 rows=14 loops=1)
Total runtime: 0.142 ms
(6 rows)
This gives me the same result as the recursive version, minus the level
column. I am porting an application from Oracle, there is a fairly large
table that is accessed by "connect by". Rewriting it as a recursive join
is not a problem, but the optimizer doesn't really use the indexes.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
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, ktm@rice.edu
Subject: Re: Postgres 9.0 has a bias against indexes
In-Reply-To: <4D41959B.8020901@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