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:
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).
