Thank you for your input.
When doing an "ORDER BY measurement_time DESC" I had hoped for the query
planner to firstly query the most recent "child" table, i.e. the table
holding current month's data, and then move-on to the oldest table, since
it knows the partition is based on measurement_time. I
Spiros Ioannou writes:
> It is ORDER BY measurement_time, not measurement_id, and measurement_time
> is used to create the partition. So the planner should know the correct
> order, but instead it seems to query tables in the wrong order.
The planner does not know that, and even if it attempted t
>
>
> > EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE
> > measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' ORDER BY
> > measurement_time DESC LIMIT 1;
> >
> > This seems to fail, scanning all tables. Do you think this can be
> improved
> > at all ? The query plan of the above
Hi,
> @Kyotaro HORIGUCHI
> thanks for your reply and time Kyotaro,
Not at all.
> Using the following query
> EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE
> measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' AND
> measurement_time >= '2015-01-01 00:00:00+0' LIMIT 1;
>
> pro
@Rob Sargent: sorry Rob, not sure what you are asking.
@Kyotaro HORIGUCHI
thanks for your reply and time Kyotaro,
Using the following query
EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE
measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' AND
measurement_time >= '2015-01-01 00:0
Hello,
Your constraint column is of 'timestamp with time zone' and the
query uses 'date'. The comparison between them is an operator
based on a static, non-immutable function so constraint exclusion
doesn't work.
SELECT o.oprname, o.oprcode, p.provolatile
FROM pg_operator o join pg_proc p on (p.
I don't understand having both UUID and time stamp in your PK? The first is by
defn. unique and the second might be.
Sent from my iPhone
> On Jan 19, 2015, at 6:12 AM, Spiros Ioannou wrote:
>
> Hello group,
> we have a timeseries table, and we tried to partition it by month (with
> pg_partma
