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@xxxxxxxxxxxxxxxxxxxxx> Light Year Software, LLC http://lightyearsoftware.com ZingLists: Stay organized, and share lists online. http://zinglists.com ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
