Re: Postgres 9.0 has a bias against indexes

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