I have postgresql 8.4.15 on Ubuntu 10.04 and this query:
SELECT MAX(probeTable.PROBE_ALARM_EVENT_ID) AS MAX_EVENT_ID
FROM ALARM_EVENT eventTable
INNER JOIN ALARM_EVENT_PROBE probeTable
ON eventTable.ALARM_EVENT_ID = probeTable.ALARM_EVENT_ID
WHERE probeTable.PROBE_ID = 2
which is running slower than it could. Table definitions and explain
analyze output below.
The first explain is the current plan (uses sequential scans).
The second is after I have disabled sequential scans, and is the plan
I would prefer.
I have vacuum analyzed both tables. In terms of relevant changes to
the default postgresql.conf, we have these:
shared_buffers = 28MB
constraint_exclusion = on
I want to understand why the optimiser is choosing the plan with
sequential table scans, rather than the plan with index scans.
I am not sure how to interpret the predicted vs actual times/costs,
and want to understand why the predicted cost for the index scan plan
seems to be way off.
I have read: https://wiki.postgresql.org/images/4/45/Explaining_EXPLAIN.pdf
show server_version;
server_version
----------------
8.4.15
Table "public.alarm_event_probe"
Column | Type | Modifiers
----------------------+---------+-----------
alarm_event_id | bigint | not null
probe_id | integer | not null
probe_alarm_event_id | bigint | not null
Indexes:
"alarm_event_probe_pkey" PRIMARY KEY, btree (alarm_event_id)
"alarm_event_probe_unique" UNIQUE, btree (probe_id,
probe_alarm_event_id)
Foreign-key constraints:
"alarm_event_probe_fk" FOREIGN KEY (probe_id) REFERENCES probe(probe_id)
Triggers:
alarm_event_probe_alarm_event_foreign_key_trigger BEFORE
INSERT OR UPDATE ON alarm_event_probe FOR EACH ROW EXECUTE PROCEDURE
alarm_event_foreign_key_function()
Table "public.alarm_event"
Column | Type |
Modifiers
------------------------+-----------------------------+----------------------------------------------------------------------
alarm_event_id | bigint | not null
default nextval('alarm_event_alarm_event_id_seq'::regclass)
timestamp_device_utc | timestamp without time zone |
timestamp_manager_utc | timestamp without time zone |
timestamp_proxy_utc | timestamp without time zone | not null
timestamp_database_utc | timestamp without time zone | not null
default systimestamp()
device_name | character varying(32) | not null
device_location | character varying(4) | not null
device_type | character varying(6) | not null
device_category | character varying(32) | not null
device_instance | integer | not null
proxy_name | character varying(128) | not null
proxy_instance | character varying(32) | not null
proxy_source | character varying(256) | not null
native_alarm_id | character varying(32) |
alarm_name | character varying(64) | not null
alarm_severity | integer |
alarm_description | character varying(1024) | not null
org_code | integer | not null default 348
domain_id | integer |
service_state | integer | not null default 0
proactive | boolean | not null
default true
alarm_id | bigint |
Indexes:
"alarm_event_pkey" PRIMARY KEY, btree (alarm_event_id)
Check constraints:
"alarm_event_alarm_severity_range" CHECK (alarm_severity >= 0
AND alarm_severity <= 5)
"alarm_event_service_state_range" CHECK (service_state >= 0
AND service_state <= 2)
Foreign-key constraints:
"alarm_event_domain_fk" FOREIGN KEY (domain_id) REFERENCES
domain(domain_id)
"alarm_event_organisation_fk" FOREIGN KEY (org_code)
REFERENCES organisation(org_code)
Triggers:
alarm_event_1_raw_trigger AFTER INSERT ON alarm_event FOR EACH
ROW EXECUTE PROCEDURE alarm_event_raw_trigger_function()
alarm_event_2_trigger_a AFTER INSERT OR DELETE OR UPDATE ON
alarm_event FOR EACH ROW EXECUTE PROCEDURE
alarm_event_trigger_function()
alarm_event_probe_alarm_event_delete_cascade_trigger BEFORE
DELETE ON alarm_event FOR EACH ROW EXECUTE PROCEDURE
alarm_event_delete_alarm_event_probe_function()
alarm_event_trigger_b BEFORE INSERT OR DELETE OR UPDATE ON
alarm_event FOR EACH ROW EXECUTE PROCEDURE
alarm_event_trigger_function()
alarm_event_zz_insert_trigger AFTER INSERT ON alarm_event FOR
EACH ROW EXECUTE PROCEDURE alarm_event_insert()
Number of child tables: 33 (Use \d+ to list them.)
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=451984.11..451984.12 rows=1 width=8) (actual
time=248693.886..248693.889 rows=1 loops=1)
-> Hash Join (cost=107.06..443578.92 rows=3362075 width=8)
(actual time=2521.275..248683.459 rows=1934 loops=1)
Hash Cond: (eventtable.alarm_event_id = probetable.alarm_event_id)
-> Append (cost=0.00..370989.07 rows=7772408 width=8)
(actual time=14.364..220430.509 rows=7771865 loops=1)
-> Seq Scan on alarm_event eventtable
(cost=0.00..3.00 rows=1 width=8) (actual time=0.088..0.088 rows=0
loops=1)
-> Seq Scan on alarm_event_y2011m12 eventtable
(cost=0.00..12638.54 rows=290254 width=8) (actual
time=14.267..11136.290 rows=290254 loops=1)
-> Seq Scan on alarm_event_y2011m10 eventtable
(cost=0.00..9146.57 rows=214457 width=8) (actual time=7.719..5820.900
rows=214457 loops=1)
-> Seq Scan on alarm_event_y2011m09 eventtable
(cost=0.00..1183.60 rows=27660 width=8) (actual time=0.046..111.918
rows=27660 loops=1)
-> Seq Scan on alarm_event_y2011m11 eventtable
(cost=0.00..6209.42 rows=149342 width=8) (actual time=0.055..662.708
rows=149342 loops=1)
-> Seq Scan on alarm_event_y2012m01 eventtable
(cost=0.00..8661.84 rows=207184 width=8) (actual time=0.075..943.065
rows=207184 loops=1)
-> Seq Scan on alarm_event_y2012m02 eventtable
(cost=0.00..7824.78 rows=182378 width=8) (actual time=0.051..6620.416
rows=182378 loops=1)
-> Seq Scan on alarm_event_y2012m03 eventtable
(cost=0.00..16717.50 rows=386350 width=8) (actual
time=101.301..14018.406 rows=386350 loops=1)
-> Seq Scan on alarm_event_y2012m04 eventtable
(cost=0.00..10125.17 rows=238117 width=8) (actual
time=25.155..3013.045 rows=238117 loops=1)
-> Seq Scan on alarm_event_y2012m05 eventtable
(cost=0.00..10747.73 rows=251573 width=8) (actual time=0.058..1605.062
rows=251573 loops=1)
-> Seq Scan on alarm_event_y2012m06 eventtable
(cost=0.00..16638.79 rows=387879 width=8) (actual time=0.122..4477.169
rows=387879 loops=1)
-> Seq Scan on alarm_event_y2012m07 eventtable
(cost=0.00..9675.58 rows=222658 width=8) (actual time=85.891..8504.216
rows=222658 loops=1)
-> Seq Scan on alarm_event_y2012m08 eventtable
(cost=0.00..9570.01 rows=234201 width=8) (actual
time=106.049..7978.829 rows=234201 loops=1)
-> Seq Scan on alarm_event_y2012m09 eventtable
(cost=0.00..12731.91 rows=300791 width=8) (actual time=5.516..5596.174
rows=300791 loops=1)
-> Seq Scan on alarm_event_y2012m10 eventtable
(cost=0.00..11052.83 rows=266483 width=8) (actual time=0.064..1159.065
rows=266483 loops=1)
-> Seq Scan on alarm_event_y2012m11 eventtable
(cost=0.00..9540.78 rows=226778 width=8) (actual time=0.045..878.269
rows=226778 loops=1)
-> Seq Scan on alarm_event_y2012m12 eventtable
(cost=0.00..8110.64 rows=208464 width=8) (actual time=0.061..883.399
rows=208464 loops=1)
-> Seq Scan on alarm_event_y2013m01 eventtable
(cost=0.00..13391.78 rows=272178 width=8) (actual
time=0.028..10847.953 rows=270708 loops=1)
-> Seq Scan on alarm_event_y2013m02 eventtable
(cost=0.00..15720.65 rows=297365 width=8) (actual
time=134.814..8192.080 rows=297204 loops=1)
-> Seq Scan on alarm_event_y2013m03 eventtable
(cost=0.00..41027.29 rows=810229 width=8) (actual
time=11.567..8419.318 rows=805929 loops=1)
-> Seq Scan on alarm_event_y2013m04 eventtable
(cost=0.00..13382.35 rows=253135 width=8) (actual time=0.072..9731.649
rows=253329 loops=1)
-> Seq Scan on alarm_event_y2013m05 eventtable
(cost=0.00..9793.55 rows=175455 width=8) (actual time=4.434..9965.956
rows=176525 loops=1)
-> Seq Scan on alarm_event_y2013m06 eventtable
(cost=0.00..9961.74 rows=184074 width=8) (actual
time=123.567..11228.522 rows=184335 loops=1)
-> Seq Scan on alarm_event_y2013m07 eventtable
(cost=0.00..10330.15 rows=190215 width=8) (actual time=4.728..4839.910
rows=189743 loops=1)
-> Seq Scan on alarm_event_y2013m08 eventtable
(cost=0.00..8808.29 rows=160329 width=8) (actual time=8.731..2313.534
rows=161477 loops=1)
-> Seq Scan on alarm_event_y2013m09 eventtable
(cost=0.00..14232.17 rows=273617 width=8) (actual time=0.035..1367.249
rows=273621 loops=1)
-> Seq Scan on alarm_event_y2013m10 eventtable
(cost=0.00..17202.44 rows=320544 width=8) (actual time=0.105..1853.031
rows=320310 loops=1)
-> Seq Scan on alarm_event_y2013m11 eventtable
(cost=0.00..15857.97 rows=278997 width=8) (actual time=0.044..7627.316
rows=281997 loops=1)
-> Seq Scan on alarm_event_y2013m12 eventtable
(cost=0.00..15012.38 rows=278738 width=8) (actual
time=121.673..11397.349 rows=280114 loops=1)
-> Seq Scan on alarm_event_y2014m01 eventtable
(cost=0.00..17534.21 rows=331521 width=8) (actual
time=70.202..7098.822 rows=330846 loops=1)
-> Seq Scan on alarm_event_y2014m02 eventtable
(cost=0.00..8124.21 rows=151321 width=8) (actual time=22.929..1894.643
rows=151158 loops=1)
-> Seq Scan on alarm_event_y2014m03 eventtable
(cost=0.00..10.40 rows=40 width=8) (actual time=0.003..0.003 rows=0
loops=1)
-> Seq Scan on alarm_event_y2014m04 eventtable
(cost=0.00..10.40 rows=40 width=8) (actual time=0.004..0.004 rows=0
loops=1)
-> Seq Scan on alarm_event_y2014m05 eventtable
(cost=0.00..10.40 rows=40 width=8) (actual time=0.003..0.003 rows=0
loops=1)
-> Hash (cost=82.89..82.89 rows=1934 width=16) (actual
time=16.402..16.402 rows=1934 loops=1)
-> Seq Scan on alarm_event_probe probetable
(cost=0.00..82.89 rows=1934 width=16) (actual time=0.403..8.985
rows=1934 loops=1)
Filter: (probe_id = 2)
Total runtime: 248864.890 ms
(42 rows)
set enable_seqscan = false;
SET
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=502051.06..502051.07 rows=1 width=8) (actual
time=11597.236..11597.239 rows=1 loops=1)
-> Nested Loop (cost=0.00..493645.84 rows=3362085 width=8)
(actual time=151.505..11587.339 rows=1934 loops=1)
Join Filter: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using alarm_event_probe_pkey on
alarm_event_probe probetable (cost=0.00..168.49 rows=1934 width=16)
(actual time=143.484..159.889 rows=1934 loops=1)
Filter: (probe_id = 2)
-> Append (cost=0.00..254.73 rows=34 width=8) (actual
time=5.343..5.874 rows=1 loops=1934)
-> Index Scan using alarm_event_pkey on
alarm_event eventtable (cost=0.00..6.65 rows=1 width=8) (actual
time=0.008..0.008 rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2011m12_alarm_event_id_ix on alarm_event_y2011m12
eventtable (cost=0.00..7.89 rows=1 width=8) (actual time=0.378..0.378
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2011m10_alarm_event_id_ix on alarm_event_y2011m10
eventtable (cost=0.00..7.87 rows=1 width=8) (actual time=0.008..0.008
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2011m09_alarm_event_id_ix on alarm_event_y2011m09
eventtable (cost=0.00..7.79 rows=1 width=8) (actual time=0.007..0.007
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2011m11_alarm_event_id_ix on alarm_event_y2011m11
eventtable (cost=0.00..7.85 rows=1 width=8) (actual time=0.008..0.008
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m01_alarm_event_id_ix on alarm_event_y2012m01
eventtable (cost=0.00..7.87 rows=1 width=8) (actual time=0.479..0.480
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m02_alarm_event_id_ix on alarm_event_y2012m02
eventtable (cost=0.00..7.86 rows=1 width=8) (actual time=0.015..0.015
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m03_alarm_event_id_ix on alarm_event_y2012m03
eventtable (cost=0.00..7.91 rows=1 width=8) (actual time=0.016..0.016
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m04_alarm_event_id_ix on alarm_event_y2012m04
eventtable (cost=0.00..7.87 rows=1 width=8) (actual time=0.014..0.014
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m05_alarm_event_id_ix on alarm_event_y2012m05
eventtable (cost=0.00..7.88 rows=1 width=8) (actual time=0.015..0.015
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m06_alarm_event_id_ix on alarm_event_y2012m06
eventtable (cost=0.00..7.91 rows=1 width=8) (actual time=0.071..0.071
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m07_alarm_event_id_ix on alarm_event_y2012m07
eventtable (cost=0.00..7.87 rows=1 width=8) (actual time=0.016..0.016
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m08_alarm_event_id_ix on alarm_event_y2012m08
eventtable (cost=0.00..7.88 rows=1 width=8) (actual time=0.027..0.027
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m09_alarm_event_id_ix on alarm_event_y2012m09
eventtable (cost=0.00..7.89 rows=1 width=8) (actual time=0.664..0.670
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m10_alarm_event_id_ix on alarm_event_y2012m10
eventtable (cost=0.00..7.88 rows=1 width=8) (actual time=0.381..0.381
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m11_alarm_event_id_ix on alarm_event_y2012m11
eventtable (cost=0.00..7.87 rows=1 width=8) (actual time=0.308..0.309
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2012m12_alarm_event_id_ix on alarm_event_y2012m12
eventtable (cost=0.00..7.86 rows=1 width=8) (actual time=0.033..0.033
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m01_alarm_event_id_ix on alarm_event_y2013m01
eventtable (cost=0.00..7.89 rows=1 width=8) (actual time=0.032..0.032
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m02_alarm_event_id_ix on alarm_event_y2013m02
eventtable (cost=0.00..7.90 rows=1 width=8) (actual time=0.254..0.254
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m03_alarm_event_id_ix on alarm_event_y2013m03
eventtable (cost=0.00..8.03 rows=1 width=8) (actual time=0.030..0.030
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m04_alarm_event_id_ix on alarm_event_y2013m04
eventtable (cost=0.00..7.89 rows=1 width=8) (actual time=0.095..0.095
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m05_alarm_event_id_ix on alarm_event_y2013m05
eventtable (cost=0.00..7.86 rows=1 width=8) (actual time=0.023..0.023
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m06_alarm_event_id_ix on alarm_event_y2013m06
eventtable (cost=0.00..7.87 rows=1 width=8) (actual time=0.104..0.104
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m07_alarm_event_id_ix on alarm_event_y2013m07
eventtable (cost=0.00..7.87 rows=1 width=8) (actual time=0.179..0.180
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m08_alarm_event_id_ix on alarm_event_y2013m08
eventtable (cost=0.00..7.87 rows=1 width=8) (actual time=0.250..0.250
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m09_alarm_event_id_ix on alarm_event_y2013m09
eventtable (cost=0.00..7.89 rows=1 width=8) (actual time=0.626..0.627
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m10_alarm_event_id_ix on alarm_event_y2013m10
eventtable (cost=0.00..7.90 rows=1 width=8) (actual time=0.300..0.300
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m11_alarm_event_id_ix on alarm_event_y2013m11
eventtable (cost=0.00..7.90 rows=1 width=8) (actual time=0.201..0.201
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2013m12_alarm_event_id_ix on alarm_event_y2013m12
eventtable (cost=0.00..7.90 rows=1 width=8) (actual time=0.552..0.552
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2014m01_alarm_event_id_ix on alarm_event_y2014m01
eventtable (cost=0.00..7.91 rows=1 width=8) (actual time=0.323..0.324
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2014m02_alarm_event_id_ix on alarm_event_y2014m02
eventtable (cost=0.00..7.86 rows=1 width=8) (actual time=0.302..0.303
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2014m03_alarm_event_id_ix on alarm_event_y2014m03
eventtable (cost=0.00..3.87 rows=1 width=8) (actual time=0.005..0.005
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2014m04_alarm_event_id_ix on alarm_event_y2014m04
eventtable (cost=0.00..3.87 rows=1 width=8) (actual time=0.005..0.005
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
-> Index Scan using
alarm_event_y2014m05_alarm_event_id_ix on alarm_event_y2014m05
eventtable (cost=0.00..3.87 rows=1 width=8) (actual time=0.005..0.005
rows=0 loops=1934)
Index Cond: (eventtable.alarm_event_id =
probetable.alarm_event_id)
Total runtime: 11599.260 ms
(75 rows)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
