Skip site navigation (1) Skip section navigation (2)

Re: table full scan or index full scan?

From: Melton Low <softw(dot)db(at)gmail(dot)com>
To: 旭斌 裴 <peixubin(at)yahoo(dot)com(dot)cn>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: table full scan or index full scan?
Date: 2009-10-18 05:07:36
Message-ID: 23d923960910172207i489c3506ia1f230e1464b8ad8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)yahoo(dot)com(dot)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?
>
>
>
> ------------------------------
> 好玩贺卡等你发,邮箱贺卡全新上线!<http://cn.rd.yahoo.com/mail_cn/tagline/card/*http://card.mail.cn.yahoo.com/>

In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2009-10-18 05:08:46
Subject: Re: sequential scan on child partition tables
Previous:From: Scott MarloweDate: 2009-10-18 05:05:40
Subject: Re: Are folks running 8.4 in production environments? and 8.4 and slon 1.2?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group