For the reasons indicated (that is, MVCC), PG can not do a DISTINCT or the equivalent
GROUP BY from index values alone.
Ok, that makes sense. Thanks for the help everybody!
If this table is large, perhaps you could denormalize and maintain a
summary table with date (using truncation) and count, updated with
triggers on the original table. This table will presumably have a
small number of rows at the cost of doubling the times for updates,
inserts, and deletes.
Well, the inserted time, at least, is never updated, and deletions are very rare (never, so far), so I'll have a look at doing things that way. Thanks!
