Mathew, yeah, that makes sense. You'd only get better performance by
that alone if it was the count(*)'s rather than all the union joins
making things slow.
Since this thread clued me in to this potential problem, I went ahead
and changed my permission_p code to select from a new
acs_object_party_priv_map_all view rather than
acs_object_party_privilege_map, where acs_object_party_priv_map_all is
an identical view, except defined with UNION ALL rather than UNION.
I did no testing at all, but my ASSUMPTION is that this COULD give
similar performance to your solution, while being a somewhat simpler
change to stick in. In reality, it almost certainly will NOT give as
good performance, as my acs_object_party_priv_map_all is still itself
selecting from various other views, and as Yon mentioned above, it is
known that Oracle is not smart enough to optimize such cases, and
Postgres probably isn't either.
As an aside, it would be very nice to be able to specify view
definitions and the like using a language like Tcl, as doing it
directly in sqlplus (for Oracle) often means either: One, building
lots of views which themselves select from other views, which keeps
things nice and neat but causes performance problems. Or two,
repeating the same blocks of text over, and over, and over again,
which leads to errors when someone goes and changes view A but forgets
to also change views B through Z to match. Basicaly, directly writing
*.sql files and shoving them through sqlplus just doesn't make sense,
at least not when it comes to defining views.
