Aaron Bono wrote:
Each of your queries has the filter xxx >= $dt where the xxx is the
first column in each select. You could simplify the query by turning
the unioned selects into a sub-query and then putting the $dt filter in
the outer query.
It would probably have to be two subqueries unless I can find a way to
merge the differences between new and changed rows.
I don't know if this will cause performance problems though. If
PostgreSQL completes the inner query before filtering by your $dt you
may be better off leaving the $dt filters where they are.
The query is only run a few times a week so performance is largely not a
concern. I'm trying to simplify it to make adding tables less
cumbersome (as a separate effort, the schema may be modified to
normalize it, e.g., topic joins to entry via subject_id and actor_id and
a subject and actor can also appear in topic_entry's topic_id).
I know Oracle has materialized views. Does PostgreSQL also have
materialized views? If so, you could get great performance from your
views AND simplify your SQL.
AFAIK PostgreSQL does not support materialized views but it's
interesting that you mention that because in essence the query is used
to materialize a view, i.e., it's part of an INSERT / SELECT into a
table which is then joined back to the other tables to construct a web
page as well as an RSS feed.
Joe
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
