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-tp5758115p5758516.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
