Re: Why the difference in plans ?

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(at)postgresql(dot)org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance