Thread: Strange difference in query execution time

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@jongsma.org
http://www.jongsma.org

Jeremy Jongsma <jeremy@jongsma.org> writes:
> 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?

> I can provide table definitions if needed.

What would be more useful is EXPLAIN ANALYZE output (and you have
ANALYZEd all the underlying tables, no?).  This is a bit off topic
for pgsql-general though, please take it to pgsql-performance.

            regards, tom lane