Re: query planner weirdness?

On Jun 27, 2008, at 9:53 PM, Adam Rich wrote:

>
>>
>> "Bob Duffey" <bobduffey68@gmail.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.

> You could get the initial rows back without waiting
> for all 130 million to be fetched and sorted.
>
> In oracle, there is a first-rows vs. all-rows query hint for this sort
> of thing.

Cheers,
   Steve