Re: Why the difference in plans ?

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Stephen Denne <Stephen(dot)Denne(at)datamail(dot)co(dot)nz>
Cc: "postgresql performance list" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Why the difference in plans ?
Date: 2008-03-07 02:16:02
Message-ID: D13450F4-F294-46C9-A4E3-D20CB8563810@fastcrypt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


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.
> __________________________________________________________________
>
>

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Stephen Denne 2008-03-07 02:30:41 Re: Why the difference in plans ?
Previous Message Stephen Denne 2008-03-06 22:10:49 Re: Why the difference in plans ?