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

From David G Johnston
Subject Re: Querying a time range across multiple partitions
Date
Msg-id 1409956109733-5817989.post@n5.nabble.com
Whole thread Raw
In response to Querying a time range across multiple partitions  (Cal Heldenbrand <cal@fbsdata.com>)
List pgsql-general
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.


pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.
Next
From: "john.tiger"
Date:
Subject: inserting a text file via json