Thread: table full scan or index full scan?
I have a 30,000,000 records table, counts the record number to need for 40 seconds. The table has a primary key on column id; perf=# explain select count(*) from test; ... ----------------------------------------- Aggregate (cost=603702.80..603702.81 rows=1 width=0) -> Seq scan on test (cost=0.00..527681.04 rows=30408704 width=0) ... perf=# select count(*) from test; count ------------ 30408704 perf=# The postgresql database uses the table full scan.but in oracle, the similar SQL uses the index full scanning,speed quickly many than postgresql. postgresql's optimizer whether to have the necessity to make the adjustment? |
好玩贺卡等你发，邮箱贺卡全新上线！
旭斌 裴 escreveu: > The postgresql database uses the table full scan.but in oracle, the > similar SQL uses the index full scanning,speed quickly many than > postgresql. > This was discussed many times on the pgsql mailing lists. Search the archives. Also, take a look at [1]. [1] http://wiki.postgresql.org/wiki/Slow_Counting -- Euler Taveira de Oliveira http://www.timbira.com/
I don't know if this will help. In my days with Oracle and Sybase, it use to work for both. Just give PG a hint like this
select count(*) from test where id > 0;
You can try it while you wait for other on the list with more knowledge for a different idea.
Mel
On Sun, Oct 11, 2009 at 4:26 AM, 旭斌 裴 <peixubin@yahoo.com.cn> wrote:
I have a 30,000,000 records table, counts the record number to need for 40 seconds.
The table has a primary key on column id;
perf=# explain select count(*) from test;
...
-----------------------------------------
Aggregate (cost=603702.80..603702.81 rows=1 width=0)
-> Seq scan on test (cost=0.00..527681.04 rows=30408704 width=0)
...
perf=# select count(*) from test;
count
------------
30408704
perf=#
The postgresql database uses the table full scan.but in oracle, the similar SQL uses the index full scanning,speed quickly many than postgresql.
postgresql's optimizer whether to have the necessity to make the adjustment?
好玩贺卡等你发，邮箱贺卡全新上线！