f.e. querying against a 2.8-mio-records (2.800.000) table the_table SELECT count(*) FROM the_table => Seq scan -> takes about 12 sec
This cannot be made O(1) in postgres due to MVCC. You just have to live with it.
SELECT Avg(num_found) AS NumFound FROM the_table --(index on num_found) => Seq scan -> takes about 10 sec
SELECT Sum(num_found) AS TotalFound FROM the_table --(index on num_found) => Seq scan -> takes about 11 sec
Average and sum can never use an index AFAIK, in any db server. You need information from every row.
SELECT Max(date_) AS LatestDate FROM the_table --(index on date_) => Seq scan -> takes about 14 sec
Yep, that's due to postgresql's type extensibility. You should use th workaround you point out below.
But SELECT date_ AS LatestDate FROM the_table ORDER BY date_ DESC LIMIT 1; => Index scan -> takes 0.18 msec
MS SQLServer 2000: Use of an appropriate index _whenever_ aggregating.
Am I doing something wrong?
Nope.
Chris
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
