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

Re: [Fwd: query efficiency - Can I speed it up?]

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: ann hedley <ann(dot)hedley(at)ed(dot)ac(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [Fwd: query efficiency - Can I speed it up?]
Date: 2007-02-01 16:47:05
Message-ID: 19199.1170348425@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
ann hedley <ann(dot)hedley(at)ed(dot)ac(dot)uk> writes:
> Can anyone tell me if/how I can speed up this query?

Try a newer version of Postgres --- I think 8.1 is the first one that
can make decent use of that two-column index in this type of query.
Notice that in the plan, only the "go_term" column is being checked
in the index condition:

>          ->  Index Scan using gotcha_go_term_sp_id on gotcha  
> (cost=0.00..46809.29 rows=232 width=32)
>                Index Cond: ((gotcha.go_term)::text = 
> ("outer".go_term)::text)
>                Filter: (((spid)::text = 'ALP'::text) OR ((spid)::text = 
> 'ASP'::text) OR ((spid)::text = 'DIP'::text) OR ((spid)::text = 
> 'GPP'::text))

I take it from the enormous cost that there are going to be lots of rows
with the same go_term, and it's the spid filter that is cutting it down
to a reasonable number of rows ... but this plan is going to visit the
heap for every row matching go_term, because the planner isn't smart
enough to fold the OR'd restriction clause together with the join
clause to make an index condition.  It applies it as a "filter" instead
which is way way slower in this situation.  I can't tell exactly which
PG release you're using, but it's definitely older than 8.1.

			regards, tom lane

In response to

Responses

pgsql-novice by date

Next:From: Rob ShepherdDate: 2007-02-01 16:57:14
Subject: Stored Procedure to return a result set
Previous:From: A. KretschmerDate: 2007-02-01 15:24:45
Subject: Re: Send Email from Postgressql

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