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.
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.)
> What I was driving at by looking at seq_tup_read is that a small table
> isn't going to use indexes anyway, so for the small tables it's
> generally not worth worrying about indexes.
OK, so it sounds like there is a threshold to be determined where
this sort of analysis isn't very interesting.
In the interests of stirring up more interest, earlier this week I
released a plugin for Ruby on Rails that extracts these statistics
from Postgres and provides helper methods to format them nicely in
web page views. I'm using it to put DB monitors on an administrative
dashboard for a site I run.
I'd love to add some analysis logic to the plugin. If useful
suggestions can be drawn from interpreting the numbers, small badges
can show up in the web page, making the tuning process a little less
painful.
(The plugins project home page is at http://groups.google.com/group/
pgsql_stats.)
--
Steve Madsen <steve@lightyearsoftware.com>
Light Year Software, LLC http://lightyearsoftware.com
ZingLists: Stay organized, and share lists online. http://zinglists.com