Skip site navigation (1) Skip section navigation (2)

Strange difference in query execution time

From: Jeremy Jongsma <jeremy(at)jongsma(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Strange difference in query execution time
Date: 2004-08-27 14:48:00
Message-ID: 412F49A0.6050608@jongsma.org (view raw or flat)
Thread:
Lists: pgsql-general
I have a view, vw_tc_user_acccess, for determing user access to certain 
objects.  On my machine, I get the following query execution times:

1. SELECT * FROM vw_tc_user_access: 33.04ms
2. SELECT * FROM vw_tc_user_access WHERE object_type = 'FORUM': 3.49ms
3. SELECT * FROM vw_tc_user_access WHERE object_type = 'CATEGORY': 107.53ms

Queries #2 and #3 are obviously a subset of #1, simply filtered by 
object_type.  My questions are:

1) How can #3 take 30 times as long as #2, given that in my databse they 
have the exact same number of rows returned and are drawn from the exact 
same tables?
2) How is it possible for #3, a subset of #1 with a very simple WHERE 
clause, to take three times longer than #1 to execute?

The view definition is:

CREATE VIEW vw_tc_user_access AS
  SELECT DISTINCT ur.user_id AS user_id,
    arm.acl_action AS action,
    ao.acl_object_type AS object_type,
    ao.acl_object_key AS object_key
   FROM tc_acl_role_map arm
    INNER JOIN tc_acl_objects ao ON arm.acl_object_id = ao.acl_object_id
    INNER JOIN tc_user_roles ur ON ur.role_id = arm.role_id;

I can provide table definitions if needed.

-j


-- 
Jeremy Jongsma
jeremy(at)jongsma(dot)org
http://www.jongsma.org

Responses

pgsql-general by date

Next:From: Joseph ShraibmanDate: 2004-08-27 15:09:26
Subject: Re: performance of IN (subquery)
Previous:From: Bruce MomjianDate: 2004-08-27 14:47:40
Subject: Re: performance of IN (subquery)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group