Hi there,

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 ....."?

Kind Regards
Yiannis


--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Ioannis Anagnostopoulos <ioannis (AT) anatec (DOT) com> writes:
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

--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

On 05/06/2012 17:55, Tom Lane wrote:
EXECUTE, and the plan will be, more or less, calculated per call without
great performance hit?

Regards and thank you
Yiannis

--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

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:

On 05/06/2012 17:55, Tom Lane wrote:

Ioannis Anagnostopoulos<ioannis (AT) anatec (DOT) com> <ioannis (AT) anatec (DOT) com> writes:

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?

Regards and thank you
Yiannis