Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "gerrit" <gerrit(dot)vanniekerk(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2623: query optimizer not using indexes with inheritance and joins
Date: 2006-09-13 18:20:31
Message-ID: 15029.1158171631@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"gerrit" <gerrit(dot)vanniekerk(at)gmail(dot)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

In response to

pgsql-bugs by date

Next:From: Dave PageDate: 2006-09-13 18:47:39
Subject: Re: BUG #2627: syntax error in COPY using pgAdmin III Query
Previous:From: Guillermo ReischDate: 2006-09-13 18:02:37
Subject: BUG #2627: syntax error in COPY using pgAdmin III Query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group