Re: index / sequential scan problem

От: Fabian Kreitner
Тема: Re: index / sequential scan problem
Дата: ,
Msg-id: 5.1.0.14.0.20030717144625.039c4a10@195.145.148.245
(см: обсуждение, исходный текст)
Ответ на: Re: index / sequential scan problem  (Paul Thomas)
Ответы: Re: index / sequential scan problem  ("Shridhar Daithankar")
Re: index / sequential scan problem  (Jord Tanner)
Re: index / sequential scan problem  (Paul Thomas)
Список: pgsql-performance

Скрыть дерево обсуждения

index / sequential scan problem  (Fabian Kreitner, )
 Re: index / sequential scan problem  ("Shridhar Daithankar", )
  Re: index / sequential scan problem  (Fabian Kreitner, )
   Re: index / sequential scan problem  ("Shridhar Daithankar", )
 Re: index / sequential scan problem  (Paul Thomas, )
  Re: index / sequential scan problem  (Fabian Kreitner, )
   Re: index / sequential scan problem  (Paul Thomas, )
    Re: index / sequential scan problem  (Fabian Kreitner, )
     Re: index / sequential scan problem  ("Shridhar Daithankar", )
     Re: index / sequential scan problem  (Jord Tanner, )
     Re: index / sequential scan problem  (Paul Thomas, )
      Re: index / sequential scan problem  (Tom Lane, )
   Re: index / sequential scan problem  (Tom Lane, )
    Re: index / sequential scan problem  (Fabian Kreitner, )
     Re: index / sequential scan problem  (Fabian Kreitner, )
      Re: index / sequential scan problem  (Dennis Björklund, )
       Re: index / sequential scan problem  (Tom Lane, )
        Re: index / sequential scan problem  ("scott.marlowe", )
        Re: index / sequential scan problem  (Dennis Björklund, )

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



В списке pgsql-performance по дате сообщения:

От: Andrew Sullivan
Дата:
Сообщение: Re: Hardware performance
От: Bill Moran
Дата:
Сообщение: Re: Relation of indices to ANALYZE