Re: Querying a time range across multiple partitions

Cal Heldenbrand wrote
> explain analyze select time,event from logins
>   where username='bob' and hash='1234' and time in (
>     select array_agg(series)
>     from generate_series(current_date - interval '3 days', current_date,
> interval '1 day')
>     as u(series)
>   );
> ERROR: operator does not exist: timestamp without time zone = timestamp
> without time zone[]
>
> explain analyze select time,event from logins

Probably doesn't help but the above would have to be written:

WHERE time = ANY(
SELECT array_agg(...) FROM ...
)

I don't know whether the planner is smart enough to optimize on "= ANY()" -
whether statically generated or determined at runtime.

I am pretty sure that constraint exclusion must be done during the planning
stages and that you have to pass in a literal set of values (or an array)
that you generate in a previous query.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Querying-a-time-range-across-multiple-partitions-tp5817958p5817989.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.