From: | ann hedley <ann(dot)hedley(at)ed(dot)ac(dot)uk> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: [Fwd: query efficiency - Can I speed it up?] |
Date: | 2007-02-01 17:13:41 |
Message-ID: | 45C21FC5.7080305@ed.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
psql (PostgreSQL) 8.0.3
Thanks, I'll try an upgrade.
Tom Lane wrote:
> 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
>
>
--
Ann
"In a world without walls and fences - who needs Windows and Gates ?"
(unknown)
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2007-02-01 17:33:31 | Re: query efficiency - Can I speed it up? |
Previous Message | ann hedley | 2007-02-01 17:01:14 | Re: query efficiency - Can I speed it up? |