query planner weirdness?

------=_Part_13134_2402742.1214627202087
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_13134_2402742.1214627202087
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,<br><br>I&#39;m seeing some query plans that I&#39;m not
expecting.&nbsp; The table in question is reasonably big (130,000,000
rows).&nbsp; The table has a primary key, indexed by one field
(&quot;ID&quot;, of type bigint).&nbsp; Thus, I would expect the following
query to simply scan through the table using the primary key:<br>
<br>select * from &quot;T&quot; order by &quot;ID&quot;<br><br>However,
here is the result of explain:<br><br>&quot;Sort&nbsp;
(cost=39903495.15..40193259.03 rows=115905552
width=63)&quot;<br>&quot;&nbsp; Sort Key: &quot;ID&quot;&quot;<br>
&quot;&nbsp; -&gt;&nbsp; Seq Scan on &quot;T&quot;&nbsp;
(cost=0.00..2589988.52 rows=115905552
width=63)&quot;<br><br>Interestingly, if I use limit in the query (e.g.,
append &quot;limit 100&quot; to the end of the query), I get the plan I
would expect (I think -- I&#39;m not 100% sure what index scan is):<br>
<br>&quot;Limit&nbsp; (cost=0.00..380.12 rows=100
width=63)&quot;<br>&quot;&nbsp; -&gt;&nbsp; Index Scan using
&quot;T_pkey&quot; on &quot;T&quot;&nbsp; (cost=0.00..440575153.49
rows=115905552 width=63)&quot;<br><br>There does seem to be some
dependence on the size of the result set.&nbsp; If I use &quot;limit
11000000&quot;, I get the first query plan above, instead of the
second.<br>
<br>This is on PostgreSQL 8.3, running on Windows.&nbsp; I haven&#39;t
made any changes to the default server configuration.&nbsp; How can I get
postgres to use the second query plan when querying the entire
table?&nbsp; 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).<br>
<br>Any help appreciated.<br><br>Thanks,<br>Bob<br>

------=_Part_13134_2402742.1214627202087--