Re: BUG #2623: query optimizer not using indexes with inheritance and joins

"gerrit" <gerrit(dot)vanniekerk(at)gmail(dot)com> writes:
> --this is the problem - cant get this thing to use indexes on city and
> capital
> explain select * from suburb, cities where suburb.name = 'abc' and city_id
> = id ;

In CVS HEAD I get

regression=# explain select * from suburb, cities where suburb.name = 'abc' and city_id = id ;
QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop (cost=8.06..110.01 rows=6000 width=80)
Join Filter: (suburb.city_id = public.cities.id)
-> Bitmap Heap Scan on suburb (cost=4.05..13.51 rows=6 width=36)
Recheck Cond: (name = 'abc'::text)
-> Bitmap Index Scan on idx_suburb_2 (cost=0.00..4.05 rows=6 width=0)
Index Cond: (name = 'abc'::text)
-> Append (cost=4.02..16.06 rows=2 width=44)
-> Bitmap Heap Scan on cities (cost=4.02..8.03 rows=1 width=44)
Recheck Cond: (suburb.city_id = public.cities.id)
-> Bitmap Index Scan on idx_cities_1 (cost=0.00..4.02 rows=1 width=0)
Index Cond: (suburb.city_id = public.cities.id)
-> Bitmap Heap Scan on capitals cities (cost=4.02..8.03 rows=1 width=44)
Recheck Cond: (suburb.city_id = public.cities.id)
-> Bitmap Index Scan on idx_capitals_1 (cost=0.00..4.02 rows=1 width=0)
Index Cond: (suburb.city_id = public.cities.id)
(15 rows)

which I suppose is the plan you are after. Pre-8.2 is not smart enough
for this though.

regards, tom lane