From: David Johnston <polobo@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Not same plan between static and prepared query
Date: Sun, 9 Jun 2013 08:49:48 -0700 (PDT)
Message-ID: <1370792988042-5758516.post@n5.nabble.com> (raw)
In-Reply-To: <B72526FA2066E344AFD09734A487318103CB3882@falcon1.celltick.com>
References: <CAH12p1AvzORBOBhpCOYc7NHpRrYdpLei3SX4oiCn3pw+LgT4xQ@mail.gmail.com>
<007201ce62a2$56c35930$044a0b90$@kapila@huawei.com>
<B72526FA2066E344AFD09734A487318103CB3882@falcon1.celltick.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Yuri Levinsky wrote
>> We have two equals queries where the sole difference is in the limit.
>> - The first is hard coded with limit 500.
>> - The second is prepared with limit $1 ($1 is bound to 500).
>
>
>> PostgreSQL give us two different plans with a huge execution time for
>> the
> prepared query:
>
> It can generate different plan for prepared query, because optimizer uses
> default selectivity in case of bound parameters (in your case limit $1).
>
>
>> We met the same behaviour with both :
>> - PostgreSQL 8.4.8 on Windows 2008 (Prod)
>> - PostgreSQL 8.4.8 and 8.4.17 on Windows 7 (Dev)
So the planner knows it needs a limit in both cases yet for the second
situation it has no idea what the limit value will be. For a sufficiently
large value of LIMIT it will conclude that a sequential scan will be optimal
and so that is what the plan uses. However, knowing the limit is only going
to be 500 it is able to conclude that an index scan will work better.
> From PostgreSQL 9.2, it generates plan for prepared query during execution
> (Execute command) as well.
> So I think you will not face this problem in PostgreSQL 9.2 and above.
See:
http://www.postgresql.org/docs/9.2/interactive/release-9-2.html
Section E.5.3.1.3 (First Bullet)
Someone more knowledgeable than myself will need to comment on how the
performance impact was overcome but my guess is that update statements
likely avoid this behavior if the where clauses are equality conditions
since indexes (if available) are going to be the most efficient plan
regardless of the specific values. Its when, in cases like this, the
planner knows the specific value of LIMIT will matter greatly that it is
going to need to use a run-time plan. Whether during the PREPARE phase the
planner tags the resultant plan with some kind of "allow runtime plan" flag
I do not know though so maybe the first few executions will always use
run-time plans and only after N executes does the cached plan come into
effect.
Its probably worth a search and read of the mailing list but I cannot do so
at this moment.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Not-same-plan-between-static-and-prepared-query-tp5758115p57...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: polobo@yahoo.com
Subject: Re: Not same plan between static and prepared query
In-Reply-To: <1370792988042-5758516.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox