Re: index / sequential scan problem

I've seen similar behavior in my own queries. I found that reducing
random_page_cost from the default of 4 down to 2 caused the query to
choose the index, and resulted in an order of magnitude improvement on
some queries.

On Thu, 2003-07-17 at 05:50, Fabian Kreitner wrote:
> At 14:34 17.07.2003, you wrote:
>
> >On 17/07/2003 12:13 Fabian Kreitner wrote:
> >>That is what I read too and is why Im confused that the index is indeed
> >>executing faster. Can this be a problem with the hardware and/or
> >>postgress installation?
> >
> >
> >It's more likely that the OS has most of the data cached after the first
> >query and so doesn't need to re-read that data from disk when you retry
> >the query with seq scan disabled. Try something like this:
> >
> >set enable_seqscan to true;
> >explain analyze ......
> >set enable_seqscan to false;
> >explain analyze ......
> >set enable_seqscan to true;
> >explain analyze ......
> >
> >I expect you will find that the third query is also a lot faster that the
> >first query.
>
> Im afraid, no.
> Database has been stopped / started right before this.
>
> perg_1097=# set enable_seqscan to true;
> 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=0.00..56125.80 rows=15561 width=12)
> (actual time=0.28..2298.71 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: 2327.37 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.25..535.75 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: 567.94 msec
>
> EXPLAIN
> perg_1097=# set enable_seqscan to true;
> 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=0.00..56125.80 rows=15561 width=12)
> (actual time=0.13..2300.74 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: 2330.25 msec
>
> EXPLAIN
> perg_1097=#
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
Jord Tanner <jord(at)indygecko(dot)com>