Hi,
We have one query which has a left join. If we run this query without
the left join, it runs slower than with the left join.
-query with the left join:
EXPLAIN ANALYZE
SELECT
artifact.id AS id,
artifact.priority AS priority,
item.title AS title,
item.name AS name,
field_value2.value AS status,
field_value3.value AS category,
sfuser.username AS submittedByUsername,
sfuser.full_name AS submittedByFullname,
sfuser2.username AS assignedToUsername,
sfuser2.full_name AS assignedToFullname,
item.version AS version
FROM
sfuser sfuser,
relationship relationship,
item item,
field_value field_value3,
sfuser sfuser2,
project project,
field_value field_value2,
field_value field_value,
artifact artifact,
folder folder,
field_value field_value4
WHERE
artifact.id=item.id
AND item.folder_id=folder.id
AND folder.project_id=project.id
AND artifact.group_fv=field_value.id
AND artifact.status_fv=field_value2.id
AND artifact.category_fv=field_value3.id
AND artifact.customer_fv=field_value4.id
AND item.created_by_id=sfuser.id
AND relationship.is_deleted=false
AND relationship.relationship_type_name='ArtifactAssignment'
AND relationship.origin_id=sfuser2.id
AND artifact.id=relationship.target_id
AND item.is_deleted=false
AND project.path='projects.gl_coconet_performance_improveme'
AND item.folder_id='tracker3641'
AND folder.path='tracker.perf_test'
AND (field_value2.value_class='Open');
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=47645.19..89559.37 rows=1 width=155) (actual
time=4411.623..6953.329 rows=71 loops=1)
Hash Cond: ((folder.project_id)::text = (project.id)::text)
-> Nested Loop (cost=47640.91..89553.64 rows=384 width=167) (actual
time=4411.558..6953.136 rows=71 loops=1)
-> Index Scan using folder_pk on folder (cost=0.00..4.35 rows=1
width=26) (actual time=0.029..0.032 rows=1 loops=1)
Index Cond: ('tracker3641'::text = (id)::text)
Filter: ((path)::text = 'tracker.perf_test'::text)
-> Nested Loop (cost=47640.91..89545.46 rows=384 width=168) (actual
time=4411.525..6953.052 rows=71 loops=1)
-> Nested Loop (cost=47640.91..89434.35 rows=384 width=150)
(actual time=4411.508..6952.049 rows=71 loops=1)
-> Nested Loop (cost=47640.91..89296.15 rows=384
width=149) (actual time=4411.489..6950.823 rows=71 loops=1)
-> Nested Loop (cost=47640.91..89157.95 rows=384
width=162) (actual time=4411.469..6949.629 rows=71 loops=1)
-> Nested Loop (cost=47640.91..89019.74
rows=384 width=175) (actual time=4411.443..6948.289 rows=71 loops=1)
-> Nested Loop (cost=47640.91..88464.52
rows=1819 width=157) (actual time=4411.418..6947.188 rows=71 loops=1)
-> Merge Join
(cost=47640.91..83661.94 rows=2796 width=158) (actual time=4411.355..6945.443
rows=71 loops=1)
Merge Cond: ((item.id)::text =
"inner"."?column7?")
-> Index Scan using item_pk
on item (cost=0.00..176865.31 rows=97498 width=88) (actual time=117.304..2405.060
rows=71 loops=1)
Filter: ((NOT
is_deleted) AND ((folder_id)::text = 'tracker3641'::text))
-> Sort
(cost=47640.91..47808.10 rows=66876 width=70) (actual time=4273.919..4401.387
rows=168715 loops=1)
Sort Key:
(artifact.id)::text
-> Hash Join
(cost=9271.96..42281.07 rows=66876 width=70) (actual time=124.119..794.667
rows=184378 loops=1)
Hash Cond:
((artifact.status_fv)::text = (field_value2.id)::text)
-> Seq Scan on
artifact (cost=0.00..25206.14 rows=475614 width=69) (actual time=0.008..214.459
rows=468173 loops=1)
-> Hash
(cost=8285.92..8285.92 rows=78883 width=27) (actual time=124.031..124.031
rows=79488 loops=1)
-> Index
Scan using field_class_idx on field_value field_value2 (cost=0.00..8285.92
rows=78883 width=27) (actual time=0.049..60.599 rows=79488 loops=1)
Index
Cond: ((value_class)::text = 'Open'::text)
-> Index Scan using
relation_target on relationship (cost=0.00..1.71 rows=1 width=25) (actual
time=0.022..0.022 rows=1 loops=71)
Index Cond:
((artifact.id)::text = (relationship.target_id)::text)
Filter: ((NOT is_deleted) AND
((relationship_type_name)::text = 'ArtifactAssignment'::text))
-> Index Scan using sfuser_pk on sfuser
(cost=0.00..0.29 rows=1 width=42) (actual time=0.013..0.013 rows=1 loops=71)
Index Cond:
((item.created_by_id)::text = (sfuser.id)::text)
-> Index Scan using field_value_pk on
field_value field_value4 (cost=0.00..0.35 rows=1 width=13) (actual
time=0.017..0.017 rows=1 loops=71)
Index Cond: ((artifact.customer_fv)::text
= (field_value4.id)::text)
-> Index Scan using field_value_pk on field_value
(cost=0.00..0.35 rows=1 width=13) (actual time=0.015..0.015 rows=1 loops=71)
Index Cond: ((artifact.group_fv)::text =
(field_value.id)::text)
-> Index Scan using field_value_pk on field_value
field_value3 (cost=0.00..0.35 rows=1 width=27) (actual time=0.015..0.015 rows=1
loops=71)
Index Cond: ((artifact.category_fv)::text =
(field_value3.id)::text)
-> Index Scan using sfuser_pk on sfuser sfuser2
(cost=0.00..0.28 rows=1 width=42) (actual time=0.012..0.012 rows=1 loops=71)
Index Cond: ((relationship.origin_id)::text =
(sfuser2.id)::text)
-> Hash (cost=4.27..4.27 rows=1 width=12) (actual time=0.048..0.048 rows=1
loops=1)
-> Index Scan using project_path on project (cost=0.00..4.27 rows=1
width=12) (actual time=0.041..0.042 rows=1 loops=1)
Index Cond: ((path)::text =
'projects.gl_coconet_performance_improveme'::text)
Total runtime: 6966.099 ms
-same query but without the left join
EXPLAIN ANALYZE
SELECT
artifact.id AS id,
artifact.priority AS priority,
item.title AS title,
item.name AS name,
field_value2.value AS status,
field_value3.value AS category,
sfuser.username AS submittedByUsername,
sfuser.full_name AS submittedByFullname,
sfuser2.username AS assignedToUsername,
sfuser2.full_name AS assignedToFullname,
item.version AS version ,
mntr_subscription.user_id AS monitoringUserId
FROM
sfuser sfuser,
relationship relationship,
item item,
field_value field_value3,
sfuser sfuser2,
project project,
field_value field_value2,
field_value field_value,
artifact artifact
LEFT JOIN
mntr_subscription mntr_subscription
ON
mntr_subscription.object_key=artifact.id AND ((mntr_subscription.user_id='17272')),
folder folder,
field_value field_value4
WHERE
artifact.id=item.id
AND item.folder_id=folder.id
AND folder.project_id=project.id
AND artifact.group_fv=field_value.id
AND artifact.status_fv=field_value2.id
AND artifact.category_fv=field_value3.id
AND artifact.customer_fv=field_value4.id
AND item.created_by_id=sfuser.id
AND relationship.is_deleted=false
AND relationship.relationship_type_name='ArtifactAssignment'
AND relationship.origin_id=sfuser2.id
AND artifact.id=relationship.target_id
AND item.is_deleted=false
AND project.path='projects.gl_coconet_performance_improveme'
AND item.folder_id='tracker3641'
AND folder.path='tracker.perf_test'
AND (field_value2.value_class='Open');
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=117.16..102664.10 rows=1 width=167) (actual
time=392.383..3412.452 rows=71 loops=1)
Join Filter: ((folder.project_id)::text = (project.id)::text)
-> Index Scan using project_path on project (cost=0.00..4.27 rows=1
width=12) (actual time=0.040..0.041 rows=1 loops=1)
Index Cond: ((path)::text =
'projects.gl_coconet_performance_improveme'::text)
-> Nested Loop (cost=117.16..102655.03 rows=384 width=179) (actual
time=392.331..3412.303 rows=71 loops=1)
-> Index Scan using folder_pk on folder (cost=0.00..4.35 rows=1
width=26) (actual time=0.034..0.036 rows=1 loops=1)
Index Cond: ('tracker3641'::text = (id)::text)
Filter: ((path)::text = 'tracker.perf_test'::text)
-> Nested Loop (cost=117.16..102646.84 rows=384 width=180) (actual
time=392.293..3412.193 rows=71 loops=1)
-> Nested Loop (cost=117.16..102535.74 rows=384 width=162)
(actual time=392.276..3411.189 rows=71 loops=1)
-> Nested Loop (cost=117.16..102397.53 rows=384
width=161) (actual time=392.258..3409.958 rows=71 loops=1)
-> Nested Loop (cost=117.16..102259.33 rows=384
width=174) (actual time=392.239..3408.734 rows=71 loops=1)
-> Nested Loop (cost=117.16..102121.13
rows=384 width=187) (actual time=392.220..3407.479 rows=71 loops=1)
-> Nested Loop (cost=117.16..101565.91
rows=1819 width=169) (actual time=392.195..3406.360 rows=71 loops=1)
-> Nested Loop
(cost=117.16..96763.32 rows=2796 width=170) (actual time=392.150..3404.791 rows=71
loops=1)
-> Merge Join
(cost=117.16..89555.79 rows=19888 width=169) (actual time=392.092..3403.281
rows=71 loops=1)
Merge Cond:
((artifact.id)::text = (item.id)::text)
-> Merge Left Join
(cost=117.16..52509.18 rows=475614 width=81) (actual time=0.050..715.999
rows=380704 loops=1)
Merge Cond: ((artifact.id)::text =
"inner"."?column3?")
-> Index Scan
using artifact_pk on artifact (cost=0.00..51202.63 rows=475614 width=69) (actual
time=0.014..424.003 rows=380704 loops=1)
-> Sort
(cost=117.16..117.30 rows=58 width=25) (actual time=0.033..0.033 rows=0 loops=1)
Sort Key:
(mntr_subscription.object_key)::text
-> Index
Scan using mntr_subscr_usrevt on mntr_subscription (cost=0.00..115.46 rows=58
width=25) (actual time=0.018..0.018 rows=0 loops=1)
Index
Cond: ((user_id)::text = '17272'::text)
-> Index Scan using
item_pk on item (cost=0.00..176865.31 rows=97498 width=88) (actual
time=116.898..2404.612 rows=71 loops=1)
Filter: ((NOT
is_deleted) AND ((folder_id)::text = 'tracker3641'::text))
-> Index Scan using
field_value_pk on field_value field_value2 (cost=0.00..0.35 rows=1 width=27)
(actual time=0.019..0.019 rows=1 loops=71)
Index Cond:
((artifact.status_fv)::text = (field_value2.id)::text)
Filter:
((value_class)::text = 'Open'::text)
-> Index Scan using
relation_target on relationship (cost=0.00..1.71 rows=1 width=25) (actual
time=0.020..0.020 rows=1 loops=71)
Index Cond:
((artifact.id)::text = (relationship.target_id)::text)
Filter: ((NOT is_deleted) AND
((relationship_type_name)::text = 'ArtifactAssignment'::text))
-> Index Scan using sfuser_pk on sfuser
(cost=0.00..0.29 rows=1 width=42) (actual time=0.013..0.014 rows=1 loops=71)
Index Cond:
((item.created_by_id)::text = (sfuser.id)::text)
-> Index Scan using field_value_pk on
field_value field_value4 (cost=0.00..0.35 rows=1 width=13) (actual
time=0.015..0.016 rows=1 loops=71)
Index Cond: ((artifact.customer_fv)::text
= (field_value4.id)::text)
-> Index Scan using field_value_pk on field_value
(cost=0.00..0.35 rows=1 width=13) (actual time=0.015..0.015 rows=1 loops=71)
Index Cond: ((artifact.group_fv)::text =
(field_value.id)::text)
-> Index Scan using field_value_pk on field_value
field_value3 (cost=0.00..0.35 rows=1 width=27) (actual time=0.015..0.015 rows=1
loops=71)
Index Cond: ((artifact.category_fv)::text =
(field_value3.id)::text)
-> Index Scan using sfuser_pk on sfuser sfuser2
(cost=0.00..0.28 rows=1 width=42) (actual time=0.012..0.012 rows=1 loops=71)
Index Cond: ((relationship.origin_id)::text =
(sfuser2.id)::text)
Total runtime: 3413.006 ms
(43 rows)
I am having a hard time to understand why the query runs faster with the
left join.
Can you help me understanding how that is possible?
Thanks,
Anne
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
