Strange difference in query execution time

[Jeremy Jongsma]

I have a view, vw_tc_user_accc ess, for determing user access to certain
objects. On my machine, I get the following query execution times:

1. SELECT * FROM vw_tc_user_acce ss: 33.04ms
2. SELECT * FROM vw_tc_user_acce ss WHERE object_type = 'FORUM': 3.49ms
3. SELECT * FROM vw_tc_user_acce ss 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_acce ss AS
SELECT DISTINCT ur.user_id AS user_id,
arm.acl_action AS action,
ao.acl_object_t ype AS object_type,
ao.acl_object_k ey AS object_key
FROM tc_acl_role_map arm
INNER JOIN tc_acl_objects ao ON arm.acl_object_ id = ao.acl_object_i d
INNER JOIN tc_user_roles ur ON ur.role_id = arm.role_id;

I can provide table definitions if needed.

-j
--
Jeremy Jongsma
je****@jongsma. org
http://www.jongsma.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Jeremy Jongsma <je****@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org