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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
