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

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

In response to

Browse pgsql-novice by date

  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