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
