I wrote:
--- snip ---
> So - does anybody with enough insight in the planner know if it sounds
> likely that it would choose the given plans in these two cases, or if
> it's more likely that I have a tuning problem that leads to bad
> planning?
Duh. It suddenly dawned on me that I need to look closer at the plans...
The big difference in the estimated and actual row count in lines like
-> Nested Loop (cost=0.00..250.78 rows=338 width=47) (actual
time=0.100..189.676 rows=187012 loops=1)
indicates that the planner is somehow mislead by the statistics on (at
least) one of the tables, right? Any suggestions as to how I go about
investigating that further?
One thing here that is slightly confusing is the relationship between
the estimated row count of 169 in the outer loop and 6059 in the last
index scan in the partial plan below. How do they relate to each other?
-> Nested Loop (cost=0.00..452.10 rows=169 width=47) (actual
time=0.088..41.244 rows=32863 loops=1)
-> Nested Loop (cost=0.00..16.55 rows=1 width=39) (actual
time=0.031..0.035 rows=1 loops=1)
-> Index Scan using i_c_id on i (cost=0.00..8.27 rows=1
width=39) (actual time=0.016..0.017 rows=1 loops=1)
Index Cond: (c = 'bar'::text)
-> Index Scan using a_i_id_idx on a (cost=0.00..8.27 rows=1
width=78) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (i_id = i.id)
-> Index Scan using x_a_id_idx on x (cost=0.00..374.95 rows=6059
width=86) (actual time=0.055..27.219 rows=32863 loops=1)
Index Cond: (a_id = a.id)
Best regards & thanks,
Mikkel Lauritsen