Обсуждение: query planner weirdness?

Hi,

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"

However, here is the result of explain:

"Sort  (cost=39903495.15..40193259.03 rows=115905552 width=63)"
"  Sort Key: "ID""
"  ->  Seq Scan on "T"  (cost=0.00..2589988.52 rows=115905552 width=63)"

Interestingly, if I use limit in the query (e.g., append "limit 100" to the end of the query), I get the plan I would expect (I think -- I'm not 100% sure what index scan is):

"Limit  (cost=0.00..380.12 rows=100 width=63)"
"  ->  Index Scan using "T_pkey" on "T"  (cost=0.00..440575153.49 rows=115905552 width=63)"

There does seem to be some dependence on the size of the result set.  If I use "limit 11000000", I get the first query plan above, instead of the second.

This is on PostgreSQL 8.3, running on Windows.  I haven't made any changes to the default server configuration.  How can I get postgres to use the second query plan when querying the entire table?  My plan is to use a server-side cursor to iterate over the result of this query, and the second plan is non-blocking whereas the first is blocking (due to the sort operator).

Any help appreciated.

Thanks,
Bob

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

            regards, tom lane

2008/6/28 Tom Lane <tgl@sss.pgh.pa.us>:

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

2008/6/28 Adam Rich <adam.r@sbcglobal.net>:

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

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.

"Bob Duffey" <bobduffey68@gmail.com> writes:
> 2008/6/28 Steve Atkins <steve@blighty.com>:
>> 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.

The planner does in fact pay more attention to first-row cost than total
cost when given a DECLARE CURSOR instead of a regular SELECT.
Apparently that wasn't enough to make the indexscan be preferred,
though, which is a bit curious.  You might want to look at
effective_cache_size as well as random_page_cost.

            regards, tom lane

On Jun 28, 2008, at 12:53 AM, Bob Duffey wrote:
>
> 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.
>

The original query you mentioned was not using a cursor, though, it
was just a select.

Did you try it using a cursor?

Cheers,
   Steve