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

The following bug has been logged online:

Bug reference: 2623
Logged by: gerrit
Email address: gerrit.vanniekerk@gmail.com
PostgreSQL version: 8.1.3
Operating system: red hat linux
Description: query optimizer not using indexes with inheritance and
joins
Details:

Hi, I've got a problem when doing an implicit join on the parent of an
inherited table - query optimizer wants to do sequencial scans on these
tables, regardless. If I join only on the parent, or the child, it is fine.

I've tried playing with values in pg_class, but it didnt help. Also loaded
and deleted data. Hope this example explains everything:

CREATE DATABASE test
WITH OWNER = postgres
ENCODING = 'SQL_ASCII'
TABLESPACE = pg_default;

CREATE SEQUENCE city_seq;

CREATE TABLE cities (
id int4 not null DEFAULT nextval(('city_seq'::text)::regclass),
name text,
population real,
altitude int -- (in ft)
);

CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

--just something to join with
create table suburb (
city_id int4,
name text
);

create index idx_cities_1 on cities using btree(id);
create index idx_capitals_1 on capitals using btree(id);
create index idx_suburb_1 on suburb using btree(city_id);
create index idx_suburb_2 on suburb using btree(name);

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler;

--get some data in the table
CREATE OR REPLACE FUNCTION populate()
RETURNS void AS
$BODY$
BEGIN
FOR i IN 1..100000 LOOP
insert into cities values(DEFAULT, null, null, null);
insert into capitals values(DEFAULT, null, null, null, null);
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

select (populate());

analyze cities;
analyze capitals;

--these query plans are all as expected
explain select * from suburb, only cities where suburb.name = 'abc' and
city_id = id ;
explain select * from suburb, capitals where suburb.name = 'abc' and
city_id = id ;
explain select * from cities where id = 12345 ;

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

regards,
Gerrit