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
