I have two tables: controller_monitor and controller_monitor_reading.
controller_monitor
Column | Type | Modifiers
------------------------+-----------------------------+-----------------------------------------------------------------
id | integer | not null default nextval('controller_monitor_id_seq'::regclass)
sid | character varying(100) |
node_type | character varying(20) | not null
name | character varying(255) | not null
tag | character varying(255) | not null
denotation | character varying(255) | not null
alias | character varying(80) |
controller_id | integer |
value | integer |
silent | boolean |
last_reading_timestamp | timestamp without time zone | not null
payload | character varying(50) |
Indexes:
"controller_monitor_pkey" PRIMARY KEY, btree (id)
"controller_monitor_sid_key" UNIQUE CONSTRAINT, btree (sid)
"controller_monitor_tag_key" UNIQUE CONSTRAINT, btree (tag)
"ix_controller_monitor_id" hash (id)
"ix_controller_monitor_silent" btree (silent)
"ix_controller_monitor_tag" hash (tag)
Foreign-key constraints:
"controller_monitor_controller_id_fkey" FOREIGN KEY (controller_id) REFERENCES controller(id)
Referenced by:
TABLE "controller_monitor_reading" CONSTRAINT "controller_monitor_reading_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
TABLE "controller_monitor_reading_p2015_03" CONSTRAINT "controller_monitor_reading_p2015_03_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
TABLE "controller_monitor_reading_p2015_04" CONSTRAINT "controller_monitor_reading_p2015_04_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
TABLE "controller_monitor_reading_p2015_05" CONSTRAINT "controller_monitor_reading_p2015_05_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
TABLE "controller_monitor_reading_p2015_06" CONSTRAINT "controller_monitor_reading_p2015_06_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
TABLE "controller_monitor_reading_p2015_07" CONSTRAINT "controller_monitor_reading_p2015_07_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
TABLE "controller_monitor_reading_p2015_08" CONSTRAINT "controller_monitor_reading_p2015_08_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
TABLE "controller_monitor_reading_p2015_09" CONSTRAINT "controller_monitor_reading_p2015_09_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
TABLE "controller_monitor_reading_p2015_10" CONSTRAINT "controller_monitor_reading_p2015_10_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
TABLE "controller_monitor_reading_p2015_11" CONSTRAINT "controller_monitor_reading_p2015_11_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
TABLE "controller_node_monitor" CONSTRAINT "controller_node_monitor_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
controller_monitor_reading
Column | Type | Modifiers | Storage | Stats target | Description
---------------+-----------------------------+-------------------------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('controller_monitor_reading_id_seq'::regclass) | plain | |
m_denotation | character varying(255) | not null | extended | |
timestamp | timestamp without time zone | not null | plain | |
qual | integer | not null | plain | |
value | integer | not null | plain | |
silent | boolean | | plain | |
silenced_by | character varying(255) | | extended | |
monitor_id | integer | not null | plain | |
state_id | integer | | plain | |
comment | character varying(140) | | extended | |
registered_at | timestamp without time zone | default timezone('utc'::text, now()) | plain | |
issue_id | integer | | plain | |
Indexes:
"controller_monitor_reading_pkey" PRIMARY KEY, btree (id)
"ix_controller_monitor_reading_id" hash (monitor_id)
"ix_controller_monitor_reading_silent" btree (silent)
"ix_controller_monitor_reading_timestamp" btree ("timestamp")
Foreign-key constraints:
"controller_monitor_reading_issue_id_fkey" FOREIGN KEY (issue_id) REFERENCES issue(id)
"controller_monitor_reading_monitor_id_fkey" FOREIGN KEY (monitor_id) REFERENCES controller_monitor(id) ON DELETE CASCADE
"controller_monitor_reading_state_id_fkey" FOREIGN KEY (state_id) REFERENCES state(id)
Triggers:
controller_monitor_reading_part_trig BEFORE INSERT ON controller_monitor_reading FOR EACH ROW EXECUTE PROCEDURE controller_monitor_reading_part_trig_func()
Child tables: controller_monitor_reading_p2015_03,
controller_monitor_reading_p2015_04,
controller_monitor_reading_p2015_05,
controller_monitor_reading_p2015_06,
controller_monitor_reading_p2015_07,
controller_monitor_reading_p2015_08,
controller_monitor_reading_p2015_09,
controller_monitor_reading_p2015_10,
controller_monitor_reading_p2015_11
Has OIDs: no
There are some monitors that have zero readings, and joins for these tables are much slower
than joins for monitors that do have some readings.
for example, this query (selecting latest reading for some monitor) takes 40ms because
'DPVESELAYA.PP14905.LOSTU' monitor has some readings
(i.e. there are readings with monitor_id that correspond to id of this monitor):
EXPLAIN ANALYZE
SELECT controller_monitor_reading.id, controller_monitor_reading.timestamp,
controller_monitor_reading.value
FROM controller_monitor_reading
JOIN controller_monitor
ON (controller_monitor.id = controller_monitor_reading.monitor_id)
WHERE controller_monitor.tag = 'DPVESELAYA.PP14905.LOSTU'
AND controller_monitor_reading.timestamp < '2015-08-13T13:54:35.139702'::timestamp
ORDER BY controller_monitor_reading.timestamp DESC LIMIT 1 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.78..11859.58 rows=1 width=16) (actual time=40.593..40.594 rows=1 loops=1)
-> Nested Loop (cost=1.78..47432.99 rows=4 width=16) (actual time=40.591..40.591 rows=1 loops=1)
Join Filter: (controller_monitor.id = controller_monitor_reading.monitor_id)
Rows Removed by Join Filter: 12197
-> Merge Append (cost=1.78..39715.40 rows=513971 width=20) (actual time=0.060..21.255 rows=12198 loops=1)
Sort Key: controller_monitor_reading."timestamp"
-> Index Scan Backward using ix_controller_monitor_reading_timestamp on controller_monitor_reading (cost=0.28..42.45 rows=673 width=20) (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_03_timestamp_idx on controller_monitor_reading_p2015_03 (cost=0.14..44.44 rows=17 width=20) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_04_timestamp_idx on controller_monitor_reading_p2015_04 (cost=0.14..44.44 rows=17 width=20) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_05_timestamp_idx on controller_monitor_reading_p2015_05 (cost=0.14..44.44 rows=17 width=20) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_06_timestamp_idx on controller_monitor_reading_p2015_06 (cost=0.14..44.44 rows=17 width=20) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_07_timestamp_idx on controller_monitor_reading_p2015_07 (cost=0.42..6254.67 rows=159638 width=20) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_08_timestamp_idx on controller_monitor_reading_p2015_08 (cost=0.42..17526.52 rows=353592 width=20) (actual time=0.026..12.941 rows=12198 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Materialize (cost=0.00..8.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=12198)
-> Index Scan using ix_controller_monitor_tag on controller_monitor (cost=0.00..8.02 rows=1 width=4) (actual time=0.016..0.018 rows=1 loops=1)
Index Cond: ((tag)::text = 'DPVESELAYA.PP14905.LOSTU'::text)
Planning time: 1.398 ms
Execution time: 40.713 ms
(25 rows)
But the same query with a monitor that doesn't have any readings takes up to 2 seconds!
EXPLAIN ANALYZE
SELECT controller_monitor_reading.id, controller_monitor_reading.timestamp,
controller_monitor_reading.value
FROM controller_monitor
JOIN controller_monitor_reading
ON (controller_monitor.id = controller_monitor_reading.monitor_id)
WHERE controller_monitor.tag = 'DPVESELAYA.PP14905.BOXDOOR'
AND controller_monitor_reading.timestamp < '2015-08-13T13:54:35.139702'::timestamp
ORDER BY controller_monitor_reading.timestamp DESC LIMIT 1 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1.78..11859.58 rows=1 width=16) (actual time=1604.720..1604.720 rows=0 loops=1)
-> Nested Loop (cost=1.78..47432.99 rows=4 width=16) (actual time=1604.719..1604.719 rows=0 loops=1)
Join Filter: (controller_monitor.id = controller_monitor_reading.monitor_id)
Rows Removed by Join Filter: 518537
-> Merge Append (cost=1.78..39715.40 rows=513971 width=20) (actual time=0.080..847.699 rows=518537 loops=1)
Sort Key: controller_monitor_reading."timestamp"
-> Index Scan Backward using ix_controller_monitor_reading_timestamp on controller_monitor_reading (cost=0.28..42.45 rows=673 width=20) (actual time=0.022..0.442 rows=673 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_03_timestamp_idx on controller_monitor_reading_p2015_03 (cost=0.14..44.44 rows=17 width=20) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_04_timestamp_idx on controller_monitor_reading_p2015_04 (cost=0.14..44.44 rows=17 width=20) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_05_timestamp_idx on controller_monitor_reading_p2015_05 (cost=0.14..44.44 rows=17 width=20) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_06_timestamp_idx on controller_monitor_reading_p2015_06 (cost=0.14..44.44 rows=17 width=20) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_07_timestamp_idx on controller_monitor_reading_p2015_07 (cost=0.42..6254.67 rows=159638 width=20) (actual time=0.015..108.979 rows=159649 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Index Scan Backward using controller_monitor_reading_p2015_08_timestamp_idx on controller_monitor_reading_p2015_08 (cost=0.42..17526.52 rows=353592 width=20) (actual time=0.033..430.380 rows=358215 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
-> Materialize (cost=0.00..8.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=518537)
-> Index Scan using ix_controller_monitor_tag on controller_monitor (cost=0.00..8.02 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1)
Index Cond: ((tag)::text = 'DPVESELAYA.PP14905.BOXDOOR'::text)
Planning time: 1.343 ms
Execution time: 1604.855 ms
(25 rows)
I believe this is because when a join is performed, postgresql tries to find a reading
with monitor_id correspondning to my monitor's id,' finds none and somehow explodes with unnecessary extra scans.
How do I fix it, except for making dummy readings for each and every monitor?
The size of controller_monitor table is about 140k rows. The size of controller_monitor_reading table (including partitioned tables) is about 700k rows, and supposed to be much more over time. My Postgresql version is 9.4.4.
UPDATE
About Vladimir Baranov's suggested answer: I added an index on (monitor_id, timestamp), and tried the suggested query. It sped up the slower query, but slowed down the faster query.
faster query (which was 40ms):
Nested Loop (cost=1.78..36.68 rows=1 width=16) (actual time=66.797..66.805 rows=1 loops=1)
-> Index Scan using ix_controller_monitor_tag on controller_monitor (cost=0.00..8.02 rows=1 width=4) (actual time=0.022..0.028 rows=1 loops=1)
Index Cond: ((tag)::text = 'DPVESELAYA.PP14905.LOSTU'::text)
-> Limit (cost=1.78..28.64 rows=1 width=16) (actual time=66.769..66.770 rows=1 loops=1)
-> Merge Append (cost=1.78..25282.92 rows=941 width=16) (actual time=66.766..66.766 rows=1 loops=1)
Sort Key: controller_monitor_reading."timestamp"
-> Index Scan Backward using ix_monitor_id_timestamp on controller_monitor_reading (cost=0.28..11.87 rows=2 width=16) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: ((controller_monitor.id = monitor_id) AND ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone))
-> Index Scan Backward using controller_monitor_reading_p2015_03_timestamp_idx on controller_monitor_reading_p2015_03 (cost=0.14..44.48 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
-> Index Scan Backward using controller_monitor_reading_p2015_04_timestamp_idx on controller_monitor_reading_p2015_04 (cost=0.14..44.48 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
-> Index Scan Backward using controller_monitor_reading_p2015_05_timestamp_idx on controller_monitor_reading_p2015_05 (cost=0.14..44.48 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
-> Index Scan Backward using controller_monitor_reading_p2015_06_timestamp_idx on controller_monitor_reading_p2015_06 (cost=0.14..44.48 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
-> Index Scan Backward using controller_monitor_reading_p2015_07_timestamp_idx on controller_monitor_reading_p2015_07 (cost=0.42..6653.76 rows=902 width=16) (actual time=59.497..59.497 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
Rows Removed by Filter: 159649
-> Index Scan Backward using controller_monitor_reading_p2015_08_timestamp_idx on controller_monitor_reading_p2015_08 (cost=0.42..18410.50 rows=33 width=16) (actual time=7.237..7.237 rows=1 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
Rows Removed by Filter: 12197
Planning time: 1.287 ms
Execution time: 66.954 ms
(30 rows)
slower query (which was 1.6sec):
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.78..36.68 rows=1 width=16) (actual time=311.481..311.481 rows=0 loops=1)
-> Index Scan using ix_controller_monitor_tag on controller_monitor (cost=0.00..8.02 rows=1 width=4) (actual time=0.022..0.027 rows=1 loops=1)
Index Cond: ((tag)::text = 'DPVESELAYA.PP14905.BOXDOOR'::text)
-> Limit (cost=1.78..28.64 rows=1 width=16) (actual time=311.447..311.447 rows=0 loops=1)
-> Merge Append (cost=1.78..25282.92 rows=941 width=16) (actual time=311.445..311.445 rows=0 loops=1)
Sort Key: controller_monitor_reading."timestamp"
-> Index Scan Backward using ix_monitor_id_timestamp on controller_monitor_reading (cost=0.28..11.87 rows=2 width=16) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: ((controller_monitor.id = monitor_id) AND ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone))
-> Index Scan Backward using controller_monitor_reading_p2015_03_timestamp_idx on controller_monitor_reading_p2015_03 (cost=0.14..44.48 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
-> Index Scan Backward using controller_monitor_reading_p2015_04_timestamp_idx on controller_monitor_reading_p2015_04 (cost=0.14..44.48 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
-> Index Scan Backward using controller_monitor_reading_p2015_05_timestamp_idx on controller_monitor_reading_p2015_05 (cost=0.14..44.48 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
-> Index Scan Backward using controller_monitor_reading_p2015_06_timestamp_idx on controller_monitor_reading_p2015_06 (cost=0.14..44.48 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
-> Index Scan Backward using controller_monitor_reading_p2015_07_timestamp_idx on controller_monitor_reading_p2015_07 (cost=0.42..6653.76 rows=902 width=16) (actual time=61.642..61.642 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
Rows Removed by Filter: 159649
-> Index Scan Backward using controller_monitor_reading_p2015_08_timestamp_idx on controller_monitor_reading_p2015_08 (cost=0.42..18410.50 rows=33 width=16) (actual time=249.768..249.768 rows=0 loops=1)
Index Cond: ("timestamp" < '2015-08-13 13:54:35.139702'::timestamp without time zone)
Filter: (controller_monitor.id = monitor_id)
Rows Removed by Filter: 358215
Planning time: 1.316 ms
Execution time: 311.597 ms
(30 rows)
This is already much better, thanks Vladimir! But is there a way to do even better?
Best Answer
I would create an index on
(monitor_id, timestamp). It should be enough.If not, I'd use
LATERAL JOIN.Since
controller_monitor.tagis unique, there should be one seek incontroller_monitortable plus one seek incontroller_monitor_readingtable using index on(monitor_id, timestamp).update
I'm not familiar with partitioned tables and it looks like you have your main table
controller_monitor_readingpartitioned intocontroller_monitor_reading_p2015_03,controller_monitor_reading_p2015_04, ... etc. Make sure that index on(monitor_id, timestamp)exists on all of these tables.