Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x - Mailing list pgsql-performance

On Saturday 02 June 2007 11:21:41 Michael Fuhr wrote:
> If you post an example query and the
> EXPLAIN ANALYZE output then we might be able to see if the slowness
> is due to query plans.

Query 1:
(SELECT
       project.path AS rbac_project_path_string,
       role_operation.resource_name AS rbac_resource_name,
       role_operation.resource_value AS rbac_resource_value
FROM
       project project,
       role role,
       role_default_user role_default_user,
       role_operation role_operation
WHERE
       role.id=role_default_user.role_id
        AND role_default_user.project_id=project.id
        AND role.id=role_operation.role_id
        AND role.is_deleted=false
        AND role_operation.object_type_id='Scm.Repository'
        AND role_operation.operation_category='use'
        AND role_operation.operation_name='access'
        AND project.path='projects.barnes_and_nobles_college_bookse_3'
        AND project.is_deleted=false
        AND role_default_user.default_user_class_id='1'
)
UNION
(SELECT
       project.path AS rbac_project_path_string,
       role_operation.resource_name AS rbac_resource_name,
       role_operation.resource_value AS rbac_resource_value
FROM
       project project,
       role role,
       role_default_user role_default_user,
       role_operation role_operation
WHERE
       role.id=role_default_user.role_id
        AND role_default_user.project_id=project.id
        AND role.id=role_operation.role_id
        AND role.is_deleted=false
        AND role_operation.object_type_id='Scm.Repository'
        AND role_operation.operation_category='use'
        AND role_operation.operation_name='access'
        AND project.path='projects.barnes_and_nobles_college_bookse_3'
        AND project.is_deleted=false
        AND role_default_user.default_user_class_id='2'
)
UNION
(SELECT
       project.path AS rbac_project_path_string,
       role_operation.resource_name AS rbac_resource_name,
       role_operation.resource_value AS rbac_resource_value
FROM
       project project,
       role role,
       role_default_user role_default_user,
       role_operation role_operation
WHERE
       role.id=role_default_user.role_id
        AND role_default_user.project_id=project.id
        AND role.id=role_operation.role_id
        AND role.is_deleted=false
        AND role_operation.object_type_id='Scm.Repository'
        AND role_operation.operation_category='use'
        AND role_operation.operation_name='access'
        AND project.path='projects.barnes_and_nobles_college_bookse_3'
        AND project.is_deleted=false
        AND role_default_user.default_user_class_id='3'
)
UNION
(SELECT
       project.path AS rbac_project_path_string,
       role_operation.resource_name AS rbac_resource_name,
       role_operation.resource_value AS rbac_resource_value
FROM
       sfuser sfuser,
       project project,
       role role,
       projectmembership projectmembership,
       role_default_user role_default_user,
       role_operation role_operation
WHERE
       role.id=role_default_user.role_id
        AND role_default_user.project_id=project.id
        AND role.id=role_operation.role_id
        AND role.is_deleted=false
        AND role_operation.object_type_id='Scm.Repository'
        AND role_operation.operation_category='use'
        AND role_operation.operation_name='access'
        AND project.path='projects.barnes_and_nobles_college_bookse_3'
        AND project.is_deleted=false
        AND role_default_user.default_user_class_id='4'
        AND projectmembership.member_id=sfuser.id
        AND role_default_user.project_id=projectmembership.project_id
        AND sfuser.username='rtrejo'
)
UNION
(SELECT
       project.path AS rbac_project_path_string,
       role_operation.resource_name AS rbac_resource_name,
       role_operation.resource_value AS rbac_resource_value
FROM
       sfuser sfuser,
       project project,
       role role,
       role_user role_user,
       role_operation role_operation
WHERE
       role.id=role_user.role_id
        AND role_user.project_id=project.id
        AND role.id=role_operation.role_id
        AND role.is_deleted=false
        AND role_operation.object_type_id='Scm.Repository'
        AND role_operation.operation_category='use'
        AND role_operation.operation_name='access'
        AND role_user.user_id=sfuser.id
        AND project.path='projects.barnes_and_nobles_college_bookse_3'
        AND project.is_deleted=false
        AND sfuser.username='rtrejo'
);

take 0m1.693s according to 'time'
Explain attached as explain1

Query 2:
SELECT
       artifact.id AS id,
       artifact.priority AS priority,
       project.path AS projectPathString,
       project.title AS projectTitle,
       folder.project_id AS projectId,
       folder.path AS folderPathString,
       folder.title AS folderTitle,
       item.folder_id AS folderId,
       item.title AS title,
       item.name AS name,
       artifact.description AS description,
       field_value.value AS artifactGroup,
       field_value2.value AS status,
       field_value2.value_class AS statusClass,
       field_value3.value AS category,
       field_value4.value AS customer,
       sfuser.username AS submittedByUsername,
       sfuser.full_name AS submittedByFullname,
       item.date_created AS submittedDate,
       artifact.close_date AS closeDate,
       sfuser2.username AS assignedToUsername,
       sfuser2.full_name AS assignedToFullname,
       item.date_last_modified AS lastModifiedDate,
       artifact.estimated_hours AS estimatedHours,
       artifact.actual_hours AS actualHours,
       item.version AS version
FROM
       relationship relationship,
       sfuser sfuser,
       sfuser sfuser2,
       field_value field_value3,
       item item,
 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.union_gas_gdar_ebt' AND ((folder.path IN
('tracker.cutover_tasks', 'tracker.peer_review_tracker', 'tracker.tars_0', 'tracker.reviews', 'tracker.defects',
'tracker.tars','tracker.database_change_requests'))  
OR folder.path LIKE 'tracker.cutover_tasks.%' OR folder.path
LIKE 'tracker.peer_review_tracker.%' OR folder.path LIKE 'tracker.tars_0.%'
OR folder.path LIKE 'tracker.reviews.%' OR folder.path LIKE 'tracker.defects.
%' OR folder.path LIKE 'tracker.tars.%' OR folder.path
LIKE 'tracker.database_change_requests.%')))
        AND folder.project_id='proj1775'
        AND item.folder_id='tracker11923'
        AND folder.path='tracker.defects'
        AND (sfuser2.username='nobody' AND field_value2.value_class='Open');

takes 0m9.506s according to time.. it's attached as explain2

TIA, again

--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

It's not the pace of life that concerns me, it's the sudden stop at the end.