Postgres Pro
Downloads
Home   >   mailing lists

Thread: Seq Scan

Seq Scan

From
"Tyler Durden"
Date:
Hi,
I'm having some problems in performance in a simple select count(id)
from.... I have 700 000 records in one table, and when I do:

# explain select (id) from table_name;
-[ RECORD 1 ]----------------------------------------------------------------
QUERY PLAN | Seq Scan on table_name  (cost=0.00..8601.30 rows=266730 width=4)

I had created an index for id(btree), but still shows "Seq Scan".
What I'm doing wrong?

Thanks,
Tyler

Re: Seq Scan

From
Dan Harris
Date:
Tyler Durden wrote:
> Hi,
> I'm having some problems in performance in a simple select count(id)
> from.... I have 700 000 records in one table, and when I do:
>
> # explain select (id) from table_name;
> -[ RECORD 1
> ]----------------------------------------------------------------
> QUERY PLAN | Seq Scan on table_name  (cost=0.00..8601.30 rows=266730
> width=4)
>
> I had created an index for id(btree), but still shows "Seq Scan".
> What I'm doing wrong?
>
> Thanks,
> Tyler
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org

select count(*) will *always* do a sequential scan, due to the MVCC
architecture.  See archives for much discussion about this.

-Dan

Re: Seq Scan

From
Michael Glaesemann
Date:
On Jun 1, 2007, at 11:48 , Tyler Durden wrote:

> I'm having some problems in performance in a simple select count(id)
> from....

Unrestricted count() (i.e., no WHERE clause) will perform a
sequential scan. If you're looking for faster ways to store table row
count information, please search the archives, as this has been
discussed many times before.

> # explain select (id) from table_name;
> -[ RECORD
> 1 ]----------------------------------------------------------------
> QUERY PLAN | Seq Scan on table_name  (cost=0.00..8601.30
> rows=266730 width=4)

The query returns the id column value for each row in the table. The
fastest way to do this is visiting every row., i.e., a sequential
scan. Using an index would require (1) looking in the index and (2)
looking up the corresponding row.

Michael Glaesemann
grzm seespotcode net