From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Dr(dot) Kurt Ruff" <kurt(dot)ruff(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: optimizing a (simple?) query on a largeish table |
Date: | 2007-11-19 04:39:54 |
Message-ID: | 12440.1195447194@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
"Dr. Kurt Ruff" <kurt(dot)ruff(at)gmail(dot)com> writes:
> 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
Replacing the OR with a UNION or UNION ALL might help, though I also
wonder whether you've selected a compatible datatype for "attn".
The upper() calls will yield type TEXT.
[ fools around a bit... ] Another possibility, if you're using PG 8.2
or later, is to replace the "attn IN (sub-SELECT)" with "attn = ANY
(ARRAY(sub-SELECT))". This is a hack --- the planner probably ought to
think of that for itself --- but currently it doesn't.
All this advice is predicated on the assumption that there are few
enough matching rows that multiple indexscans really are a better plan
than one seqscan. Since you didn't say how many rows you expect, it's
not impossible that the plan you've got is in fact the best.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2007-11-19 15:03:41 | xpath question |
Previous Message | Decibel! | 2007-11-19 04:31:43 | Re: populating arrays with default values |