@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:00:00+0' LIMIT 1;
produces this plan:
Limit (cost=0.00..4.02 rows=1 width=67) (actual time=49.125..49.125
rows=1 loops=1)
-> Append (cost=0.00..3644.05 rows=907 width=67) (actual
time=49.122..49.122 rows=1 loops=1)
-> Seq Scan on measurement_events (cost=0.00..0.00 rows=1
width=966) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((measurement_time >= '2015-01-01
02:00:00+02'::timestamp with time zone) AND (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid))
-> Bitmap Heap Scan on *measurement_events_p2015_01*
(cost=41.73..3546.10 rows=894 width=54) (actual time=49.119..49.119 rows=1
loops=1)
Recheck Cond: ((measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >=
'2015-01-01 02:00:00+02'::timestamp with time zone))
-> Bitmap Index Scan on *measurement_events_p2015_01_pkey*
(cost=0.00..41.51 rows=894 width=0) (actual time=41.836..41.836 rows=997
loops=1)
Index Cond: ((measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >=
'2015-01-01 02:00:00+02'::timestamp with time zone))
-> Index Scan using *measurement_events_p2015_02_pkey* on
*measurement_events_p2015_02* (cost=0.14..8.16 rows=1 width=966) (never
executed)
Index Cond: ((measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >=
'2015-01-01 02:00:00+02'::timestamp with time zone))
-> Index Scan using *measurement_events_p2015_03_pkey* on
*measurement_events_p2015_03* (cost=0.14..8.16 rows=1 width=966) (never
executed)
Index Cond: ((measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid) AND (measurement_time >=
'2015-01-01 02:00:00+02'::timestamp with time zone))
... (cut for brevity)
1) Do you know if this means that the query will stop on 1st find (since it
is limit 1), or will it search all tables regardless results?
2) To improve on the above, do you (or anyone else) have any input on this:
to get the latest value from all tables, we were using the following query
(before partitioning):
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 query is as follows:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=5.57..9.99 rows=1 width=921) (actual time=5.361..5.361 rows=1
loops=1)
-> Merge Append (cost=5.57..451374.16 rows=102155 width=921) (actual
time=5.359..5.359 rows=1 loops=1)
Sort Key: measurement_events.measurement_time
-> Index Scan Backward using measurement_events_pkey on
*measurement_events* (cost=0.12..8.14 rows=1 width=966) (actual
time=0.004..0.004 rows=0 loops=1)
Index Cond: (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
-> Index Scan Backward using *measurement_events_p2014_01_pkey*
on *measurement_events_p2014_01* (cost=0.14..8.16 rows=1 width=966)
(actual time=0.002..0.002 rows=0 loops=1)
Index Cond: (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
-> Index Scan Backward using *measurement_events_p2014_02_pkey*
on *measurement_events_p2014_02* (cost=0.14..8.16 rows=1 width=966)
(actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
-> Index Scan Backward using *measurement_events_p2014_03_pkey*
on *measurement_events_p2014_03* (cost=0.14..8.16 rows=1 width=966)
(actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (measurement_source_id =
'df86917e-8df0-11e1-8f8f-525400e76ceb'::uuid)
............. (cut for brevity)
