Re: Sort of a planner regression 8.3->8.4 (due to EXISTS inlining) and related stuff

On Sun, May 16, 2010 at 7:07 PM, Andres Freund <andres(at)anarazel(dot)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