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: Thu, 6 Mar 2008 21:16:02 -0500
Message-ID: <D13450F4-F294-46C9-A4E3-D20CB8563810@fastcrypt.com> (raw)
In-Reply-To: <F0238EBA67824444BC1CB4700960CB4804D2ED1F@dmpeints002.isotach.com>
References: <E0CF6B25-5191-4AF7-8C72-E67FA2F1AE5E@fastcrypt.com>
<F0238EBA67824444BC1CB4700960CB4804D2ED1F@dmpeints002.isotach.com>
On 6-Mar-08, at 5:10 PM, Stephen Denne wrote:
> Dave Cramer wrote:
>> I have two almost identical queries. Strangely enough the one
>> that uses the index is slower ???
>
> The index scan is being used so that it can retrieve the rows in the
> name order.
> It expects that if it was to retrieve every row via the index, it
> would get about 1010 rows that matched the filter, and it knows it
> can stop after 250, so assuming the matching rows are evenly
> distributed it thinks it can stop after having read only a quarter
> of the rows.
>
> However only 129 rows matched. Consequently it had to read every row
> in the table anyway, seeking a fair bit as the read order was
> specified by the index rather than in sequential order, and it also
> had to read the index. These extra costs were much larger than
> reading the lot sequentially, and sorting 129 resulting rows.
>
> The first query picked a sequential scan as it thought it was only
> going to get 11 results, so was expecting that the limit wasn't
> going to come into play, and that every row would have to be read
> anyway.
>
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.
Dave
> Regards,
> Stephen Denne.
>
> Disclaimer:
> At the Datamail Group we value team commitment, respect,
> achievement, customer focus, and courage. This email with any
> attachments is confidential and may be subject to legal privilege.
> If it is not intended for you please advise by reply immediately,
> destroy it and do not copy, disclose or use it in any way.
>
> __________________________________________________________________
> This email has been scanned by the DMZGlobal Business Quality
> Electronic Messaging Suite.
> Please see http://www.dmzglobal.com/services/bqem.htm for details.
> __________________________________________________________________
>
>
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: <D13450F4-F294-46C9-A4E3-D20CB8563810@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