Re: Querying a time range across multiple partitions - Mailing list pgsql-general

From Cal Heldenbrand
Subject Re: Querying a time range across multiple partitions
Date
Msg-id CAAcwKhchAnfCWkSvvyAUUd-7tU+neZgSX9tA4HAbvpARi8Wh0Q@mail.gmail.com
Whole thread Raw
In response to Re: Querying a time range across multiple partitions  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
Thanks Jeff!  That's what I wanted to confirm, that I need to hard code / pregenerate my dates in the query.  I was mainly curious why it didn't work with current_date, and that answers it.

And BTW, all my inserts happen on the most recent table, so my insert trigger with a linear search is ordered date descending.  It seems to work fine since my use case is to insert data once, and never change it again.  Of course my only problem is when a select query confuses the planner, and searches my entire set.  ;-) 

Thanks,

--Cal

On Sun, Sep 7, 2014 at 2:44 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Fri, Sep 5, 2014 at 10:31 AM, Cal Heldenbrand <cal@fbsdata.com> wrote:

explain analyze select time,event from logins
  where username='bob' and hash='1234' and time > current_date - interval '1 week';

 Result  (cost=0.00..765.11 rows=1582 width=14)
   ->  Append  (cost=0.00..765.11 rows=1582 width=14)
         ->  Seq Scan on logins  (cost=0.00..0.00 rows=1 width=66)

              Filter: (((username)::text = 'bob'::text) AND ((hash)::text = '1234'::text) AND ("time" > (('now'::text)::date - '7 days'::interval)))
         ->  Index Scan using logins_20100501_username_time on logins_20100501 logins  (cost=0.01..0.48 rows=1 width=14)

               ...

This shows that it's attempting to run the query against all of my 1500 child tables. 

I believe the problem is that the planner (which does the partition pruning) is not willing to materialize the value of current_date, so it can't use a specific value to prune partitions.  After all, the date might change between the planner and the executor, if you leave the plan open for a long time, or make the call very close to midnight.

You will probably have to select the current_date (or get it from your system, or cron, or whoever triggers the script), and then hardcode it into the query.

Cheers,

Jeff

pgsql-general by date:

Previous
From: Emanuel Calvo
Date:
Subject: Re: inserting a text file via json
Next
From: damien clochard
Date:
Subject: Introducing Open PostgreSQL Monitoring (OPM)