On Fri, May 3, 2013 at 3:52 PM, Anne Rosset <arosset@xxxxxxxxxx> wrote: > We saw a little bit improvement by increasing the min_pool_size but again I see a bigvariation in the time the query is executed. Here is the query: > > 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.title AS folderTitle, > psrdb-> item.folder_id AS folderId, > psrdb-> item.title AS title, > psrdb-> item.name AS name, > psrdb-> field_value2.value AS status, > psrdb-> field_value3.value AS category, > psrdb-> sfuser.username AS submittedByUsername, > psrdb-> sfuser.full_name AS submittedByFullname, > psrdb-> sfuser2.username AS assignedToUsername, > psrdb-> sfuser2.full_name AS assignedToFullname, > psrdb-> item.version AS version, > psrdb-> CASE when ((SELECT > psrdb(> mntr_subscription.user_id AS userId > psrdb(> FROM > psrdb(> mntr_subscription mntr_subscription > psrdb(> WHERE > psrdb(> artifact.id=mntr_subscription.object_key > psrdb(> AND mntr_subscription.user_id='user1439' > psrdb(> )= 'user1439') THEN 'user1439' ELSE null END AS monitoringUserId, > psrdb-> tracker.icon AS trackerIcon, > psrdb-> tracker.remaining_effort_disabled AS remainingEffortDisabled, > psrdb-> tracker.actual_effort_disabled AS actualEffortDisabled, > psrdb-> tracker.estimated_effort_disabled AS estimatedEffortDisabled > psrdb-> FROM > psrdb-> field_value field_value2, > psrdb-> field_value field_value, > psrdb-> sfuser sfuser2, > psrdb-> field_value field_value3, > psrdb-> field_value field_value4, > psrdb-> item item, > psrdb-> project project, > psrdb-> relationship relationship, > psrdb-> tracker tracker, > 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-> AND relationship.origin_id=sfuser2.id > psrdb-> AND artifact.id=relationship.target_id > psrdb-> AND item.is_deleted=false > psrdb-> AND ((artifact.priority=3)) > psrdb-> AND (project.path='projects.psr-pub-13') > psrdb-> AND item.folder_id=tracker.id > psrdb-> ; (*please* stop top-posting). What is the cpu profile of the machine while you are threading the query out? if all cpu peggged @ or near 100%, it's possible seeing spinlock contention on some of the key index buffers -- but that's a long shot. More likely it's planner malfeasance. Are you running this *exact* query across all threads or are the specific parameters changing (and if so, maybe instead the problem is that specific arguments sets providing bad plans?) This is a classic case of surrogate key design run amok, leading to bad performance via difficult to plan queries and/or poorly utilized indexes. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
