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.

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?



---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?


Reply via email to