Re: index / sequential scan problem

From: Paul Thomas
Subject: Re: index / sequential scan problem
Date: ,
Msg-id: 20030717133445.A27991@bacon
(view: Whole thread, Raw)
In response to: Re: index / sequential scan problem  (Fabian Kreitner)
Responses: Re: index / sequential scan problem  (Fabian Kreitner)
List: pgsql-performance

Tree view

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 by date:

From: Tom Lane
Date:
Subject: Re: Relation of indices to ANALYZE
From: Andrew Sullivan
Date:
Subject: Re: Hardware performance