pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dave Cramer <pg@fastcrypt.com>
To: Stephen Denne <Stephen.Denne@datamail.co.nz>
Cc: postgresql performance list <pgsql-performance@postgresql.org>
Subject: Re: Why the difference in plans ?
Date: Fri, 7 Mar 2008 06:21:56 -0500
Message-ID: <1EA4326F-42CD-4112-B993-E10FFA22C6CE@fastcrypt.com> (raw)
In-Reply-To: <F0238EBA67824444BC1CB4700960CB4804D2EE90@dmpeints002.isotach.com>
References: <E0CF6B25-5191-4AF7-8C72-E67FA2F1AE5E@fastcrypt.com>
	<F0238EBA67824444BC1CB4700960CB4804D2ED1F@dmpeints002.isotach.com>
	<D13450F4-F294-46C9-A4E3-D20CB8563810@fastcrypt.com>
	<F0238EBA67824444BC1CB4700960CB4804D2EE90@dmpeints002.isotach.com>


On 6-Mar-08, at 9:30 PM, Stephen Denne wrote:

>> The strange thing of course is that the data is exactly the same for
>> both runs, the tables have not been changed between runs, and I did
>> them right after another. Even more strange is that the seq scan is
>> faster than the index scan.
>
> It is not strange at all, since both queries read ALL the rows in  
> your table, checking each and every row to see whether it matched  
> your predicates.
>
> The sequential scan read them in the order they are on the disk,  
> meaning your disk didn't have to seek as much (assuming low file  
> fragmentation).
>
> The index scan again reads all the rows in your table, but reads  
> them in the order they were in the index, which is probably quite  
> different from the order that they are on the disk, so the disk had  
> to seek a lot. In addition, it had to read the index.
>
OK, that makes sense.

So given that the predicates are essentially the same why would the  
planner decide to use or not use the index ?

>
>
> -- 
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org 
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance




view thread (13+ messages)  latest in thread

Message-ID: <1EA4326F-42CD-4112-B993-E10FFA22C6CE@fastcrypt.com>
Permalink:  ../1EA4326F-42CD-4112-B993-E10FFA22C6CE@fastcrypt.com/
Also on:    postgresql.org/message-id/1EA4326F-42CD-4112-B993-E10FFA22C6CE@fastcrypt.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pg@fastcrypt.com, Stephen.Denne@datamail.co.nz
  Subject: Re: Why the difference in plans ?
  In-Reply-To: <1EA4326F-42CD-4112-B993-E10FFA22C6CE@fastcrypt.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox