Re: Interpreting statistics collector output

On Wed, Aug 15, 2007 at 01:26:02PM -0400, Steve Madsen wrote:
> On Aug 15, 2007, at 11:52 AM, Decibel! wrote:
> >I can't really think of a case where a seqscan wouldn't return all the
> >rows in the table... that's what it's meant to do.
>
> Isn't a sequential scan the only option if an appropriate index does
> not exist? E.g., for a query with a WHERE clause, but none of the
> referenced columns are indexed.

Yes, and that seqscan is going to read the entire table and then apply a
filter.

> Put another way: consider a large table with no indexes.
> seq_tup_read / seq_scan is the average number of rows returned per
> scan, and if this is a small percentage of the row count, then it
> seems reasonable to say an index should help query performance.
> (With the understanding that it's fewer common rather than many
> unique queries.)

decibel=# select * into i from generate_series(1,99999) i;
SELECT
decibel=# select seq_scan, seq_tup_read from pg_stat_all_tables where relname='i';
seq_scan | seq_tup_read
----------+--------------
0 | 0
(1 row)

decibel=# select * from i where i=1;
i
---
1
(1 row)

decibel=# select seq_scan, seq_tup_read from pg_stat_all_tables where relname='i';
seq_scan | seq_tup_read
----------+--------------
1 | 99999
(1 row)
--
Decibel!, aka Jim Nasby decibel(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)