Dotaz na optimalizaci - indexy

[Karel Zak]

On Fri, Jun 20, 2003 at 02:03:00PM +0200, Ing. Pavel Janousek wrote:

 Zdar,

> select id, ... from TableA where id not in (select id from TableB) and
> (now() - cas) < 3000000 order by cas desc;

# EXPLAIN SELECT id FROM A WHERE id NOT IN (SELECT ID FROM B);
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Seq Scan on a  (cost=200000022.50..200000023.57 rows=3 width=4)
   Filter: (NOT (hashed subplan))
   SubPlan
     ->  Seq Scan on b  (cost=100000000.00..100000020.00 rows=1000 width=4)


# EXPLAIN SELECT id FROM A WHERE NOT EXISTS (SELECT id FROM B WHERE A.id=B.id);
                               QUERY PLAN                               
------------------------------------------------------------------------
 Seq Scan on a  (cost=100000000.00..100000015.53 rows=3 width=4)
   Filter: (NOT (subplan))
   SubPlan
     ->  Index Scan using b_pkey on b  (cost=0.00..4.82 rows=2 width=4)
           Index Cond: ($0 = id)

 Coz?

    Karel

-- 
 Karel Zak  <zakkr na zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/