I might be misunderstanding but I think that what Tom meant is that the EXPLAIN does not try to guess whether the constraint will actually exclude some data, so it show you a pessimistic estimate of the cost.
If I'm right, using EXPLAIN ANALYZE should show that the query considers any constraint clauses in your query.
Cheers, Marcelo Lacerda
On 06/06/2012 05:10 AM, Ioannis Anagnostopoulos wrote:
I came across the following problem. Say I have a select query that given the appropriate where clauses it will only query the correct partitioned tables and not all of them. That can be examined by using the EXPLAIN. However the same select query used in a stored procedure will transverse all the tables regardless of the where clause parameters passed by the user as variables of the stored procedure . Is there any work around other than composing an EXECUTE "select ....."?
Yeah, if the WHERE clauses that correspond to the partitioning constraints contain parameters, the planner can't prove the exclusions hold. So you have to use EXECUTE to get a one-shot plan. This will be better in 9.2 (which, basically, will automatically recognize that it needs to use one-shot plans).
regards, tom lane
When you say better in 9.2 you mean that we will be able to avoid the EXECUTE, and the plan will be, more or less, calculated per call without great performance hit?
