index / sequential scan problem - Mailing list pgsql-performance

Hi all,

Im currently taking my first steps with db optimizations and am wondering
whats happening here and if/how i can help pg choose the better plan.

Thanks,
   Fabian

 >>>

psql (PostgreSQL) 7.2.2

perg_1097=# VACUUM ANALYZE ;
VACUUM
perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
perg_1097-#     from    notiz_objekt a
perg_1097-#     where not exists
perg_1097-#     (
perg_1097(#       select  1
perg_1097(#       from    notiz_gelesen b
perg_1097(#       where   ma_id  = 2001
perg_1097(#       and     ma_pid = 1097
perg_1097(#       and     a.notiz_id = b.notiz_id
perg_1097(#     )
perg_1097-# ;
NOTICE:  QUERY PLAN:

Seq Scan on notiz_objekt a  (cost=0.00..56125.80 rows=15561 width=12)
(actual time=0.28..2305.52 rows=31122 loops=1)
   SubPlan
     ->  Seq Scan on notiz_gelesen b  (cost=0.00..1.79 rows=1 width=0)
(actual time=0.07..0.07 rows=0 loops=31122)
Total runtime: 2334.42 msec

EXPLAIN
perg_1097=# SET enable_seqscan to false;
SET VARIABLE
perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
perg_1097-#     from    notiz_objekt a
perg_1097-#     where not exists
perg_1097-#     (
perg_1097(#       select  1
perg_1097(#       from    notiz_gelesen b
perg_1097(#       where   ma_id  = 2001
perg_1097(#       and     ma_pid = 1097
perg_1097(#       and     a.notiz_id = b.notiz_id
perg_1097(#     )
perg_1097-# ;
NOTICE:  QUERY PLAN:

Seq Scan on notiz_objekt a  (cost=100000000.00..100111719.36 rows=15561
width=12) (actual time=0.24..538.86 rows=31122 loops=1)
   SubPlan
     ->  Index Scan using idx_notiz_gelesen_2 on notiz_gelesen
b  (cost=0.00..3.57 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=31122)
Total runtime: 570.75 msec

EXPLAIN
perg_1097=#

perg_1097=# \d notiz_objekt;
       Table "notiz_objekt"
   Column  |  Type   | Modifiers
----------+---------+-----------
  notiz_id | integer |
  obj_id   | integer |
  obj_typ  | integer |
Indexes: idx_notiz_objekt_1,
          idx_notiz_objekt_2

perg_1097=# \d notiz_gelesen;
                                   Table "notiz_gelesen"
   Column  |           Type           |                     Modifiers
----------+--------------------------+----------------------------------------------------
  notiz_id | integer                  |
  ma_id    | integer                  |
  ma_pid   | integer                  |
  stamp    | timestamp with time zone | default ('now'::text)::timestamp(6)
with time zone
  anzeigen | character varying        |
Indexes: idx_notiz_gelesen_1,
          idx_notiz_gelesen_2

perg_1097=#

perg_1097=# select count(*) from notiz_objekt;
  count
-------
  31122
(1 row)

perg_1097=# select count(*) from notiz_gelesen;
  count
-------
     45
(1 row)

perg_1097=#

idx_notiz_gelesen_1 (ma_id,ma_pid)
idx_notiz_gelesen_2 (notiz_id)