Re: Deterioration in performance when query executed in multi threads

On Fri, May 3, 2013 at 3:52 PM, Anne Rosset <arosset(at)collab(dot)net> 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