> Wow. I hadn't known about generate_series, but there are a bunch of
> places I've needed it.
It's a wonder tool :).
> But I think there is something I can do: I can just do a query of the
> transaction table sorted by start time. My graph tool can keep a
Reading the previous paragraphs I was just about to suggest this. This
is a much more elegant method...you are reaping the benefits of having
normalized your working set. You were trying to denormalize it back to
what you were used to. Yes, now you can drop your index and simplify
your queries...normalized data is always more 'natural'.
> Mind you, I still think PostgreSQL should be able to perform that
> sorted union fast. Maybe sometime I'll have enough free time to take
> my first plunge into looking at a database query planner.
I'm not so sure I agree, by using union you were basically pulling two
independent sets (even if they were from the same table) that needed to
be ordered. There is zero chance of using the index here for ordering
because you are ordering a different set than the one being indexed.
Had I not been able to talk you out of de-normalizing your table I was
going to suggest rigging up a materialized view and indexing that:
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
Merlin