Re: query planner weirdness?

2008/6/28 Steve Atkins <steve(at)blighty(dot)com>:

>
> On Jun 27, 2008, at 9:53 PM, Adam Rich wrote:
>
>
>>
>>> "Bob Duffey" <bobduffey68(at)gmail(dot)com> writes:
>>>
>>>> I'm seeing some query plans that I'm not expecting. The table in
>>>>
>>> question
>>>
>>>> is reasonably big (130,000,000 rows). The table has a primary key,
>>>>
>>> indexed
>>>
>>>> by one field ("ID", of type bigint). Thus, I would expect the
>>>>
>>> following
>>>
>>>> query to simply scan through the table using the primary key:
>>>>
>>>
>>> select * from "T" order by "ID"
>>>>
>>>
>>> This is not wrong, or at least not obviously wrong. A full-table
>>> indexscan is often slower than seqscan-and-sort. If the particular
>>> case is wrong for you, you need to look at adjusting the planner's
>>> cost parameters to match your environment. But you didn't provide any
>>> evidence that the chosen plan is actually worse than the alternative
>>> ...
>>>
>>
>> I think I understand what Bob's getting at when he mentions blocking.
>> The seqscan-and-sort would return the last record faster, but the
>> indexscan returns the first record faster. If you're iterating
>> through the records via a cursor, the indexscan behavior would be
>> more desirable.
>>
>
> If you're iterating through the records with a cursor, the plan may
> be different, IIRC - weighted to provide first row quickly, as opposed
> to the query that was run that's weighted to provide last row quickly.
>
I agree, and I was hoping that would be the case, but as it happens it
wasn't. Anyway, reducing random_page_cost seems to have resulted in the
"right" plan being selected.