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

Reply via email to