Re: Deterioration in performance when query executed in multi threads

On Fri, May 3, 2013 at 3:52 PM, Anne Rosset <arosset@collab.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
isexecuted. 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