Aggregates and Performance

[csn]

Anybody have a rule of thumb for balance between using aggregates and optimizing performance (i.e. number of rows, traffic levels, types of aggregates/queries, etc.)? I've had previous apps hit the wall with aggregate queries called 1+ times a seconds on tables with 10k-100k's of records.

I'm debating this because I have an app that needs counts of items according to type, i.e.

select count(*)
from items
where type_id = x;

I was going to just add a "count" column to the types table and add/subtract each time an item is added/removed, but the items have start/end datetimes, so the number of active items changes over time. So...I can either have each connection call the aggregate queries one or more times (up to hundreds of connections per minute) , or set up a cron script that updates the counts once per minute. There will be 1,000-10,000's of items.

[Sxooter]

The real answer is, of course, a question. How large on average is the count gonna be.

If you've got a table where you wannt do "select count(*) from table" with no where clause, then it'll be slow. If you do a lot of "select state, count(state) from table group by state" then indexes should work /be useful there.

So, it's not so much the number of rows in the table, though that's important, it's how many rows you'll be returning to the aggregate, since it technically will need to go across the number.

The second query up there I listed (the one for states) is actually very fast, even on a large table, as long as there aren't a lot of unique entries in the field (only 50 states...) the only "data" going into the aggregate should the index on states. The index is much smaller, and therefore much faster than a seq scan on the table.

In Postgresql using explain analyze will show you the number of rows different parts of the query will throw around, and their width. It helps a lot to see those numbers inside the query of how much has to move from one part to another.