> 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