table partitioning and parametric queries
Coniglio Sgabbiato
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
Laurenz Albe
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 '
|| 'FROM tbl WHERE epoch > ' || ivl::text || ' ORDER BY epoch';
Then the value used is a constant for sure.
Yours,
Laurenz Albe
Coniglio Sgabbiato
[CUT]
> As to your original question, I am not certain what the
> documentation means with a "stable" function (why the quotes?).
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.
> 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 '
> || 'FROM tbl WHERE epoch > ' || ivl::text || ' ORDER BY epoch';
>
> Then the value used is a constant for sure.
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
Laurenz Albe
> 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?
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
Coniglio Sgabbiato
I'll experiment with this extensively, will let you know if it will work
and how :)