Hi, I am considering to use table partitioning for a new application,
reading the documentation I found this:

"Constraint exclusion only works when the query's WHERE clause contains
constants. A parameterized query will not be optimized, since the
planner cannot know which partitions the parameter value might select at
run time. For the same reason, "stable" functions such as CURRENT_DATE
must be avoided."

does this mean that current_timestamp(0) in example below won't be
evaluated from planner in regard to contraints? So all partition tables
indexes will be scanned? ("$something" are perl variables)

select epoch, etcetc
from tbl where epoch > extract('epoch' from
current_timestamp(0)-'$intrvl'::interval) order by epoch

how can I circumvent this behavior? could I write a stored procedure
like this?

begin
declare
ivl int;
begin
ivl:= extract('epoch' from
current_timestamp(0)-'$intervallo'::interval)

return query select epoch, etcetc
from tbl where epoch > ivl order by epoch;
return;
end;


will be ivl evaluated as constant?
how can I monitor the actual behavior of the planner?
how can I do an explain select from within the function?

Thank you in advance for advices

Coniglio Sgabbiato wrote:
The stored procedure as you wrote it will certainly not work,
because the query will be parsed as "where epoch > $1" where
$1 is a parameter. Remember that the same query could be called
with different values of ivl if you call the function repeatedly.

As to your original question, I am not certain what the
documentation means with a "stable" function (why the quotes?).

It *could* be that they mean functions that are implemented as
a cast of a constant:

text=> EXPLAIN VERBOSE SELECT current_date;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Output: ('now'::text)::date
(2 rows)

Then maybe clock_timestamp() would be ok:

test=> EXPLAIN VERBOSE SELECT clock_timestamp();
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Output: clock_timestamp()
(2 rows)

I recommend that you experiment and find out what works.

On the other hand, there is one technique that will certainly
work, and that is to use a function that uses dynamic SQL.

In your example above:

RETURN QUERY EXECUTE 'SELECT epoch, etcetc '
Then the value used is a constant for sure.

Yours,
Laurenz Albe

Laurenz Albe ha scritto:
[CUT]
I was wondering about the same thing, it could be that they call
current_date "stable" because it is constant within the same day. It
could be they warn you about it because the planner it is not aware of
the "steadiness" of current_date output.

I think this is definitively the right approach, I'll go for "execute",
thank you very much for the advice.

PS: I read in the documentation that postgres should keep good
performances for a number of partitions less than 1 thousand. Do you
think that 730 tables with 5 millions row each will be manageable by a
postgres installation that right now manages well 1 table with 10
millions rows?

Anselmo Canfora

Coniglio Sgabbiato wrote:
I must admit that I have no experience in that area.

But then performance is a thing that is difficult to predict -
it depends on your hardware, operating system, storage,
database configuration, physical database design and so on.

I would experiment and run performance tests with a test system
if I want to be sure.

Yours,
Laurenz Albe

Laurenz Albe ha scritto:
I'll experiment with this extensively, will let you know if it will work
and how