Resent. As I could not see my mail in the mailing list after about two hours.
On Thursday, August 21, 2014 1:43 PM, Patrick Dung <patrick_dkt@xxxxxxxxxxxx> wrote:
Hi Postgresql users,
I have a master table with two partition table (food_2013p, food_2014p).
I found that
when I use SELECT + 'now' constant, constraint exclusion works, (it skipped the 2013 partition).
EXPLAIN ANALYZE
SELECT *
FROM food
WHERE food.post_timestamp >= ('now'::date - interval '1 month')::date AND food.post_timestamp <= 'now'
ORDER BY food.post_timestamp DESC
LIMIT 30;
SELECT *
FROM food
WHERE food.post_timestamp >= ('now'::date - interval '1 month')::date AND food.post_timestamp <= 'now'
ORDER BY food.post_timestamp DESC
LIMIT 30;
But when I put the query inside view, 'now' is converted to the timestamp when I run the create view.
So the view becomes:
WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval)::date AND food.post_timestamp <=
'2014-08-21 13:38:29.642347'::timestamp without time zone
This is not dynamic.
When I use now(), the query will scan other partition tables. I know this is a restriction of partition on non-immutable function.
Would it be possible or a feature request to take advantage of the partition table with query like this?
Thanks and regards,
Patrick
