query planner weirdness?
От | Bob Duffey |
---|---|
Тема | query planner weirdness? |
Дата | |
Msg-id | 14422aad0806272126o5567f6bdtebe89a2694488efa@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: query planner weirdness?
|
Список | 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
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
В списке pgsql-general по дате отправления: