On Jun 27, 2008, at 9:53 PM, Adam Rich wrote:
"Bob Duffey" <bobduffey68@xxxxxxxxx> 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
