On Sun, May 16, 2010 at 7:07 PM, Andres Freund <andres@anarazel.de> wrote:
> Reducing the (large and ugly, automatically generated queries) to a
> reproducible testcase I ended up with the following pattern:
>
> explain SELECT 1
> FROM
> c
> WHERE
> EXISTS (
> SELECT *
> FROM a
> JOIN b USING (b_id)
> WHERE b.c_id = c.c_id)
> AND c.value = 1;
>
> 8.3 planned this to:
>
> Index Scan using c_value_key on c (cost=0.00..24.83 rows=1 width=0)
> Index Cond: (value = 1)
> Filter: (subplan)
> SubPlan
> -> Nested Loop (cost=0.00..16.56 rows=1 width=12)
> -> Index Scan using b__c_id on b (cost=0.00..8.27 rows=1
> width=8)
> Index Cond: (c_id = $0)
> -> Index Scan using a__b_id on a (cost=0.00..8.27 rows=1
> width=8)
> Index Cond: (a.b_id = b.b_id)
>
> Which is quite good for such a kind of query.
>
> From 8.4 onwards this gets planned to
> [something bad]
I believe this is a result of a limitation we've discussed
previously, namely, that the planner presently uses a limited,
special-case kludge to consider partial index scans, and the executor
uses another kludge to execute them.
http://archives.postgresql.org/pgsql-hackers/2009-09/msg00525.php
http://archives.postgresql.org/pgsql-hackers/2009-10/msg00994.php
http://archives.postgresql.org/pgsql-hackers/2009-12/msg01755.php
I believe that Tom is planning to fix this for 9.1.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company