Re: index / sequential scan problem

От: Paul Thomas
Тема: Re: index / sequential scan problem
Дата: ,
Msg-id: 20030717133445.A27991@bacon
(см: обсуждение, исходный текст)
Ответ на: Re: index / sequential scan problem  (Fabian Kreitner)
Ответы: Re: index / sequential scan problem  (Fabian Kreitner)
Список: 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, )

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.

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+


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

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