> You could
> check by temporarily dropping the actor_case_assignment_both index and
> seeing what plan you get.
Here is the result:
Index Scan using actor_upper_full_name on actor (cost=0.00..1544484.16
rows=3051 width=40)
SubPlan
-> Nested Loop (cost=0.00..21275.72 rows=42 width=24)
-> Index Scan using actor_case_assignment_fk1 on
actor_case_assignment (cost=0.00..9221.62 rows=2696 width=12)
-> Index Scan using case_data_case_id on case_data
(cost=0.00..4.46 rows=1 width=12)
Lightning-fast, but I need that index on both ids for other purposes.
The problem is that I need the index on both foreign keys because I use it
to kick out duplicate entry attempts during my load process. (Duplicate
actors are ok, and duplicate cases are ok, but an actor can only be assigned
to a case once, so the combination must be unique.)
Fortunately, your info on the function index not using stats got me thinking
in a profitable direction:
I'm using the "exists" subquery only because when I used "distinct", I got
even worse performance. I think this was because the planner chose not to
use my index on upper(actor_full_name) when I simply joined all of the
tables. If I reword the query to use "distinct", and eliminate also the
upper() on my constraint, the query really flies.
So I think my solution will be to add a new column called
"upper_actor_full_name" to my "actor" table, and add a bit of code to my
load process that will populate this field with upper(actor_full_name). It's
a bit of a kludge, but should work until the day that you get to adding
stats for function indexes.
Thanks for the help.
-Nick
--------------------------------------------------------------------------
Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/