Use of 'now' constant datatype in view to take advantage of partitioned table

From: Patrick Dung <patrick_dkt(at)yahoo(dot)com(dot)hk>
To: Pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Use of 'now' constant datatype in view to take advantage of partitioned table
Date: 2014-08-21 05:43:14
Message-ID: 1408599794.24265.YahooMailNeo@web193505.mail.sg3.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Patrick Dung 2014-08-21 07:20:24 Re: Use of 'now' constant datatype in view to take advantage of partitioned table
Previous Message Jov 2014-08-21 05:39:30 Re: Linux replication to FreeBSD problem