Re: Querying a time range across multiple partitions

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(dot)janes(at)gmail(dot)com> wrote:

> On Fri, Sep 5, 2014 at 10:31 AM, Cal Heldenbrand <cal(at)fbsdata(dot)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
>