"Nick Fankhauser" <nickf@ontko.com> writes:
>> 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.
Hmm. If the outer side of the nestloop were actually hitting 2696 rows
on average, it wouldn't be "lightning fast". So the planner's failure
to choose this plan is probably due to this overestimate. You said you
were still on 7.1.*, right? It'd be interesting to know if 7.2 gets
this right; it has more detailed stats and hopefully would make a better
estimate of the number of matches.
regards, tom lane