The following bug has been logged online:

Bug reference: 2623
Logged by: gerrit
Email address: gerrit.vanniekerk (AT) gmail (DOT) 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

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

"gerrit" <gerrit.vanniekerk (AT) gmail (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster