On Sun, May 16, 2010 at 7:07 PM, Andres Freund <and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
