From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Andres Freund <andres(at)anarazel(dot)de> |
Cc: | pgsql-hackers(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: Sort of a planner regression 8.3->8.4 (due to EXISTS inlining) and related stuff |
Date: | 2010-05-17 00:09:28 |
Message-ID: | AANLkTiljmhny69Qdqg77ChlGT8kbwGaCE0C8rGMuX0Z8@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2010-05-17 00:18:45 | Re: pg_upgrade and extra_float_digits |
Previous Message | Andres Freund | 2010-05-16 23:15:45 | Re: Sort of a planner regression 8.3->8.4 (due to EXISTS inlining) and related stuff |