[PERFORM] index / sequential scan problem

Fabian Kreitner Thu, 17 Jul 2003 01:58:16 -0700

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=# 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)


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[PERFORM] index / sequential scan problem

Reply via email to