Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Patrick Dung
Date:
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@yahoo.com.hk> 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;

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


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

From
Patrick Dung
Date:
An update, inspired by Date LastDay - PostgreSQL wiki
 
 
 
 
 
 
Date LastDay - PostgreSQL wiki
Snippets Date LastDay() Works with PostgreSQL Any version Written in SQL Depends on Nothing by Scott Bailey 'Artacus'
Preview by Yahoo
 

1. Create a function like:

CREATE OR REPLACE FUNCTION now_function_ts_notz()
  RETURNS timestamp without time zone AS
$BODY$
  SELECT (NOW())::timestamp without time zone;
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;

2. Create my View like this:
CREATE OR REPLACE VIEW v_recent_items AS
 SELECT *
   FROM food
  WHERE food.post_timestamp >= (now_function_ts_notz() - '1 mon'::interval)::timestamp without time zone AND food.post_timestamp <= now()::timestamp without time zone
  ORDER BY food.post_timestamp DESC
 LIMIT 30;

While running 'EXPLAIN ANALYZE select * from v_recent_items'
I found that it skipped the partition table food_2013p.
Is it a valid work around? Or there is other better or elegant way?

Thanks and regards,
Patrick


On Thursday, August 21, 2014 3:21 PM, Patrick Dung <patrick_dkt@yahoo.com.hk> wrote:


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@yahoo.com.hk> 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;

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




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

From
Ken Tanzer
Date:

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;


I think the problem is that you're using 'now'::date in your first example, which gets frozen.  You can use now() or current_timestamp or current_date to get dynamic results.

CREATE  VIEW test_now AS SELECT current_timestamp as current_ts, now() as now_function,'now'::timestamp AS now_literal;

(wait a couple of seconds)

SELECT * FROM test_now;

          current_ts           |         now_function          |        now_literal         
-------------------------------+-------------------------------+----------------------------
 2014-08-21 01:25:54.147004-07 | 2014-08-21 01:25:54.147004-07 | 2014-08-21 01:18:22.207073
(1 row)

You'll see that the last column is frozen while the other two stay current.

Cheers,
Ken



--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

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

From
Patrick Dung
Date:
Hi all,

I got a typo in last mail, below is the updated one, with changes in bold fonts.

The method is inspired by Date LastDay - PostgreSQL wiki

1. Create a function like:

CREATE OR REPLACE FUNCTION now_function_ts_notz()
  RETURNS timestamp without time zone AS
$BODY$
  SELECT (NOW())::timestamp without time zone;
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;

2.
CREATE VIEW v_recent_food AS
 SELECT *
   FROM food
  WHERE food.post_timestamp >= (now_function_ts_notz() - '1 mon'::interval)::date
  AND food.post_timestamp <= now_function_ts_notz()::timestamp without time zone
  ORDER BY food.post_timestamp DESC
 LIMIT 30;

While running 'EXPLAIN ANALYZE select * from v_recent_items'
I found that it skipped the partition table food_2013p.
Is it a valid work around? Or there is other better or elegant way?

Thanks and regards,
Patrick



On Thursday, August 21, 2014 4:01 PM, Patrick Dung <patrick_dkt@yahoo.com.hk> wrote:


An update, inspired by Date LastDay - PostgreSQL wiki
 
 
 
 
 
 
Date LastDay - PostgreSQL wiki
Snippets Date LastDay() Works with PostgreSQL Any version Written in SQL Depends on Nothing by Scott Bailey 'Artacus'
Preview by Yahoo
 

1. Create a function like:

CREATE OR REPLACE FUNCTION now_function_ts_notz()
  RETURNS timestamp without time zone AS
$BODY$
  SELECT (NOW())::timestamp without time zone;
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;

2. Create my View like this:
CREATE OR REPLACE VIEW v_recent_items AS
 SELECT *
   FROM food
  WHERE food.post_timestamp >= (now_function_ts_notz() - '1 mon'::interval)::timestamp without time zone AND food.post_timestamp <= now()::timestamp without time zone
  ORDER BY food.post_timestamp DESC
 LIMIT 30;

While running 'EXPLAIN ANALYZE select * from v_recent_items'
I found that it skipped the partition table food_2013p.
Is it a valid work around? Or there is other better or elegant way?

Thanks and regards,
Patrick


On Thursday, August 21, 2014 3:21 PM, Patrick Dung <patrick_dkt@yahoo.com.hk> wrote:


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@yahoo.com.hk> 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;

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






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

From
Patrick Dung
Date:
Hi Ken,

Thanks for reply.

1.
The problem is that using 'now' in VIEW, the resulting VIEW will hard code the current timestamp.
It is not dynamic.
If I use write the view like this:

 WHERE food.post_timestamp >= ('now'::date - interval '1 month')::timestamp without time zone
 AND food.post_timestamp <= 'now'::timestamp without time zone

The VIEW will be created like this:
  WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval) AND food.post_timestamp <= '2014-08-21 17:32:21.787179'::timestamp without time zone


2.
now() is dynamic but it scan all the partitioned tables.

Thanks and regards,
Patrick


On Thursday, August 21, 2014 4:27 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:



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;


I think the problem is that you're using 'now'::date in your first example, which gets frozen.  You can use now() or current_timestamp or current_date to get dynamic results.

CREATE  VIEW test_now AS SELECT current_timestamp as current_ts, now() as now_function,'now'::timestamp AS now_literal;

(wait a couple of seconds)

SELECT * FROM test_now;

          current_ts           |         now_function          |        now_literal         
-------------------------------+-------------------------------+----------------------------
 2014-08-21 01:25:54.147004-07 | 2014-08-21 01:25:54.147004-07 | 2014-08-21 01:18:22.207073
(1 row)

You'll see that the last column is frozen while the other two stay current.

Cheers,
Ken



--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.


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

From
Alban Hertroys
Date:
On 21 August 2014 11:36, Patrick Dung <patrick_dkt@yahoo.com.hk> wrote:
2.
now() is dynamic but it scan all the partitioned tables.

Most likely you partitioned on a timestamp without time zone, while now() returns a timestamp with time zone. The possible time zone difference causes that the database doesn't know in which partition to look.

In a similar vein, the function you defined to return your timestamp you marked 'immutable', which it should most definitely not be; time moves on, after all.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.