Re: no index-usage on aggregate-functions?

On Tue, 29 Jun 2004, Harald Lau (Sector-X) wrote:

> > Average and sum can never use an index AFAIK, in any db server.  You 
> > need information from every row.
> 
> Take a look at the SQLSrv-pendant:
> create index x_1 on the_table (num_found)
> select avg(num_found) from the_table
> -> Index Scan(OBJECT:([midata].[dbo].[THE_TABLE].[x_1])

But is it really faster is the question?

This sum needs all the values in that column. As far as I know it uses the
index because it uses less space on disk and thus is a little faster due
to less IO. In pg the index doesn't work like that, so in pg it's faster
to sum all values using the table itself.

If you have a WHERE clause to only sum some values, then pg will use an
index (if applicable) and you will see a speedup.

For min and max the situation is different, there an index can give you
the answer without scanning all rows. For that the workaround exist in pg.  
The pg aggregate functions are very general and no one have special cased
min/max yet. Until that happen the work around works and is fast.

> So, it seems that PG is not soooo well suited for a datawarehouse and/or
> performing extensive statistics/calculations/reportings on large tables,
> is it?

I don't see how you can say that from your example. Just because it uses
an index for the sum above does not mean that it is a lot faster. It still 
have to do as many additions as pg has to do.

Sure, mvcc is best when you have both read and writes. But it should still
be comparable in speed even if you only do reads.

-- 
/Dennis Björklund


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html