Re: A *short* planner question

> 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/