From: | "Dr(dot) Kurt Ruff" <kurt(dot)ruff(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | optimizing a (simple?) query on a largeish table |
Date: | 2007-11-19 04:15:27 |
Message-ID: | c7d13d760711182015n5cdaaf0dt14accc9e04313e5a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi.
I'm just getting started with PostgreSQL. Porting over an huge Oracle
database application for a fun first project :^)
I've got the following query which I'm trying to run on a 4.2 million row table:
SELECT ActionItems.*
FROM ActionItems
WHERE
attn=upper(SESSION_USER)
or attn in (
select upper(groname)
from pg_group
where (select oid from pg_roles where rolname = SESSION_USER) = ANY(grolist)
)
ORDER BY dateTimeCreated
That is, "match any ActionItem directed to me personally, or to the
groups to which I belong".
It currently takes about 8 seconds.
I have indexes on both used columns in the large table ("attn" and
"dateTimeCreated"), but it doesn't seem to be using them --- I've
attached the "EXPLAIN" result below.
Any ideas about what's going on here? How can I reduce the execution time?
Thanks,
Kurt
---
Sort (cost=1242644.46..1247909.54 rows=2106033 width=200)
Sort Key: datetimecreated
-> Seq Scan on actionitems (cost=573.01..186430.80 rows=2106033 width=200)
Filter: (((attn)::text = upper(("session_user"())::text)) OR
(hashed subplan))
SubPlan
-> Seq Scan on pg_authid (cost=5.10..573.01 rows=2 width=64)
Filter: ((NOT rolcanlogin) AND ($0 = ANY ((subplan))))
InitPlan
-> Seq Scan on pg_authid (cost=0.00..5.10 rows=1 width=4)
Filter: (rolname = "session_user"())
SubPlan
-> Seq Scan on pg_auth_members (cost=0.00..4.01
rows=15 width=4)
Filter: (roleid = $1)
From | Date | Subject | |
---|---|---|---|
Next Message | Decibel! | 2007-11-19 04:31:43 | Re: populating arrays with default values |
Previous Message | Oliver Elphick | 2007-11-17 08:03:13 | Re: Accessing tables in one database from another |