Search Postgresql Archives

Re: Querying a time range across multiple partitions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
On Fri, Sep 5, 2014 at 10:31 AM, Cal Heldenbrand <cal@xxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux