Skip site navigation (1) Skip section navigation (2)

query planner weirdness?

From: "Bob Duffey" <bobduffey68(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: query planner weirdness?
Date: 2008-06-28 04:26:37
Message-ID: 14422aad0806272126o5567f6bdtebe89a2694488efa@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2008-06-28 04:36:11
Subject: Re: query planner weirdness?
Previous:From: Bill MoranDate: 2008-06-27 22:37:45
Subject: Re: freebsd + postgresql 8.3.3 = 100% of cpu usage on stats collector?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group