Search Postgresql Archives

Re: table full scan or index full scan?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Real quick, plain text is preferred on these lists over html.  I don't
care myself so much.

On Sun, Oct 11, 2009 at 7:17 PM, 旭斌 裴 <peixubin@xxxxxxxxxxxx> 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.

Yep, PostgreSQL isn't Oracle.  It's a trade off.  In pgsql indexes
don't contain visibility info, so all index lookups have to eventually
hit the table itself.  So you either do indexlookup -> table lookup,
repeat as many times as you have index lookups or you just hit the
table since you gotta go there anyway.

On the  bright side, this makes updates faster since you don't have to
lock both table and index and write to both at the same time anymore.

> postgresql's optimizer whether to have the necessity to make the adjustment?

Sorry, it's an architectural difference.  Are you testing in a
realistic scenario including both reads and writes to the database to
see if postgresql is faster overall and identify problem areas that
pop up there?

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux