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

[gerrit]

The following bug has been logged online:

Bug reference:      2623
Logged by:          gerrit
Email address:      [EMAIL PROTECTED]
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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings