Database I/O and other performance questions.

Hi everyone,

What typical/max database I/O throughputs are people getting for simple
selects of datasets cached in memory but not explicitly clustered?
100KB/sec? 1MB/sec? 10MB/sec? (assuming << 8KB rows).

I see transfer rates that match the I/O speed of the disk.

Does a "select count(*) from tablename where ..." actually retrieve all
columns from all the selected rows or does it only count the rows?

Yes, it reads all rows.

That is to say:
would/should select count(*) be slower than select
count(averysmallcolumnmaybeboolean)

Same speed.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:

would/should select count(*) be slower than select
count(averysmallcolumnmaybeboolean)

There is actually a semantic difference there, see
http://www.postgresql.org/devel-corner/docs/postgres/sql-expressions.htm#SYNTAX-AGGREGATES
count(*) means the count of selected rows, but count(foo) means the
number of selected rows where the variable or expression is not NULL.

count(*) should be a fraction faster, because it doesn't expend any
cycles to check whether the specific column is NULL. The "*" in this
syntax doesn't mean "all columns" the way it does in a select list,
it's just a dummy.

regards, tom lane