Обсуждение: 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

"gerrit" <gerrit.vanniekerk@gmail.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