Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Sean DavisDate: 2007-11-19 15:03:41
Subject: xpath question
Previous:From: Decibel!Date: 2007-11-19 04:31:43
Subject: Re: populating arrays with default values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group