I'd be curious to see what would happen if you added claimnum as a
field in your view. I don't have a complete understanding of the
postgres internals in terms of how it is able to push outer clauses
down in to its views, but I think it might be able to optimize in
that fashion if it is able to add a WHERE clause internally to the
view, which it can't do in the case of claimnum since it doesn't
exist in the view.
I added the claimnum and this actually slowed it down a bit because of
the additional group by, however I then changed my where clause to
filter on the view's claimnum rather than tbl_claim's claimnum, and I
got the results I wanted.
It seems to me that in the future I should always construct my views
such that my WHERE clauses end up on the view and not on any tables that
they join with. The only problem with this is that very often I don't
know in advance what fields the client will want to search by, and now
I'll end up with two steps instead of one (modify my code AND modify the
view), however the speed increase is an order of magnatude and well
worth it.
Thanks!
John
---------------------------(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
