Re: optimizing a (simple?) query on a largeish table

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"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