|
Hi all, We are running a stress test that executes one select query with multiple threads. The query executes very fast (10ms). It returns 100 rows. I see deterioration in the performance when we have multiple threads executing the query. With 100 threads, the query takes between 3s and 8s. I suppose there is a way to tune our database. What are the parameters I should look into? (shared_buffer?, wal_buffer?) srdb=> explain analyze SELECT psrdb-> artifact.id AS id, psrdb-> artifact.priority AS priority, psrdb-> project.path AS projectPathString, psrdb-> project.title AS projectTitle, psrdb-> folder.project_id AS projectId, psrdb-> folder.path AS folderPathString, psrdb-> folder.title AS folderTitle, psrdb-> item.folder_id AS folderId, psrdb-> item.planning_folder_id AS planningFolderId, psrdb-> item.title AS title, psrdb-> item.name AS name, psrdb-> artifact.description AS description, psrdb-> field_value.value AS artifactGroup, psrdb-> field_value2.value AS status, psrdb-> field_value2.value_class AS statusClass, psrdb-> field_value3.value AS category, psrdb-> field_value4.value AS customer, psrdb-> sfuser.username AS submittedByUsername, psrdb-> sfuser.full_name AS submittedByFullname, psrdb-> item.date_created AS submittedDate, psrdb-> artifact.close_date AS closeDate, psrdb-> sfuser2.username AS assignedToUsername, psrdb-> sfuser2.full_name AS assignedToFullname, psrdb-> item.date_last_modified AS lastModifiedDate, psrdb-> artifact.estimated_effort AS estimatedEffort, psrdb-> artifact.actual_effort AS actualEffort, psrdb-> artifact.remaining_effort AS remainingEffort, psrdb-> artifact.points AS points, psrdb-> artifact.autosumming AS autosumming, psrdb-> item.version AS version psrdb-> FROM psrdb-> field_value field_value2, psrdb-> sfuser sfuser2, psrdb-> field_value field_value3, psrdb-> field_value field_value, psrdb-> field_value field_value4, psrdb-> item item, psrdb-> project project, psrdb-> relationship relationship, psrdb-> artifact artifact, psrdb-> sfuser sfuser, psrdb-> folder folder psrdb-> WHERE psrdb-> artifact.id=item.id psrdb-> AND item.folder_id=folder.id psrdb-> AND folder.project_id=project.id psrdb-> AND artifact.group_fv=field_value.id psrdb-> AND artifact.status_fv=field_value2.id psrdb-> AND artifact.category_fv=field_value3.id psrdb-> AND artifact.customer_fv=field_value4.id psrdb-> AND item.created_by_id=sfuser.id psrdb-> AND relationship.is_deleted=false psrdb-> AND relationship.relationship_type_name='ArtifactAssignment' psrdb->
psrdb-> AND relationship.origin_id=sfuser2.id
psrdb-> AND artifact.id=relationship.target_id psrdb-> AND item.is_deleted=false
psrdb-> AND folder.is_deleted=false
psrdb-> AND folder.project_id='proj1032'
psrdb-> AND item.folder_id='tracker1213'
psrdb-> AND folder.path='tracker.trackerName';
QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..117.32 rows=3 width=1272) (actual time=7.003..9.684 rows=100 loops=1)
-> Nested Loop (cost=0.00..116.69 rows=2 width=1271) (actual time=6.987..8.820 rows=100 loops=1)
Join Filter: ((item.created_by_id)::text = (sfuser.id)::text)
-> Seq Scan on sfuser (cost=0.00..7.65 rows=65 width=30) (actual time=0.013..0.053 rows=65 loops=1) -> Materialize (cost=0.00..107.10 rows=2 width=1259) (actual time=0.005..0.100 rows=100 loops=65)
-> Nested Loop (cost=0.00..107.09 rows=2 width=1259) (actual time=0.307..5.667 rows=100 loops=1)
-> Nested Loop (cost=0.00..106.45 rows=2 width=1263) (actual time=0.294..4.841 rows=100 loops=1)
-> Nested Loop (cost=0.00..105.82 rows=2 width=1267) (actual time=0.281..3.988 rows=100 loops=1) -> Nested Loop (cost=0.00..105.18 rows=2 width=1271) (actual time=0.239..3.132 rows=100 loops=1)
-> Nested Loop (cost=0.00..104.61 rows=2 width=1259) (actual time=0.223..2.457 rows=100 loops=1)
-> Nested Loop (cost=0.00..16.55 rows=1 width=1099) (actual time=0.095..0.096 rows=1 loops=1)
-> Index Scan using project_pk on project (cost=0.00..8.27 rows=1 width=1114) (actual time=0.039..0.039 rows=1 loops=1)
Index Cond: ((id)::text = 'proj1032'::text)
-> Index Scan using folder_pk on folder (cost=0.00..8.27 rows=1 width=67) (actual time=0.054..0.055 rows=1 loops=1)
Index Cond: ((folder.id)::text = 'tracker1213'::text)
Filter: ((NOT folder.is_deleted) AND ((folder.project_id)::text = 'proj1032'::text) AND (folder.path = 'tracker.trackerName'::text))
-> Nested Loop (cost=0.00..88.04 rows=2 width=169) (actual time=0.127..2.323 rows=100 loops=1)
-> Nested Loop (cost=0.00..63.19 rows=3 width=168) (actual time=0.090..1.309 rows=100 loops=1)
-> Index Scan using item_folder on item (cost=0.00..21.78 rows=5 width=77) (actual time=0.046..0.265 rows=100 loops=1)
Index Cond: ((folder_id)::text = 'tracker1213'::text) Filter: (NOT is_deleted)
-> Index Scan using artifact_pk on artifact (cost=0.00..8.27 rows=1 width=91) (actual time=0.009..0.009 rows=1 loops=100)
Index Cond: ((artifact.id)::text = (item.id)::text)
-> Index Scan using relation_target on relationship (cost=0.00..8.27 rows=1 width=18) (actual time=0.009..0.009 rows=1 loops=100)
Index Cond: ((relationship.target_id)::text = (artifact.id)::text)
Filter: ((NOT relationship.is_deleted) AND ((relationship.relationship_type_name)::text = 'ArtifactAssignment'::text))
-> Index Scan using sfuser_pk on sfuser sfuser2 (cost=0.00..0.27 rows=1 width=30) (actual time=0.005..0.005 rows=1 loops=100)
Index Cond: ((sfuser2.id)::text = (relationship.origin_id)::text) -> Index Scan using field_value_pk on field_value field_value3 (cost=0.00..0.30 rows=1 width=14) (actual time=0.007..0.007 rows=1 loops=100)
Index Cond: ((field_value3.id)::text = (artifact.category_fv)::text)
-> Index Scan using field_value_pk on field_value (cost=0.00..0.30 rows=1 width=14) (actual time=0.007..0.007 rows=1 loops=100)
Index Cond: ((field_value.id)::text = (artifact.group_fv)::text)
-> Index Scan using field_value_pk on field_value field_value4 (cost=0.00..0.30 rows=1 width=14) (actual time=0.007..0.007 rows=1 loops=100)
Index Cond: ((field_value4.id)::text = (artifact.customer_fv)::text)
-> Index Scan using field_value_pk on field_value field_value2 (cost=0.00..0.30 rows=1 width=19) (actual time=0.007..0.007 rows=1 loops=100)
Index Cond: ((field_value2.id)::text = (artifact.status_fv)::text)
Total runtime: 10.210 ms (37 rows) Thanks for your help, Anne |
