Re: Help speeding up delete

On 11/14/05, Steve Wampler <swampler(at)noao(dot)edu> wrote:

> However, even that seems to have a much higher cost than I'd expect:
>
> lab.devel.configdb=# explain delete from "tmp_table2" where id in
> (select id from tmp_table2 where name='obsid' and value = 'oid080505');
> NOTICE: QUERY PLAN:
>
> Seq Scan on tmp_table2 (cost=0.00..65705177237.26 rows=769844 width=6)
> SubPlan
> -> Materialize (cost=42674.32..42674.32 rows=38 width=50)
> -> Seq Scan on tmp_table2 (cost=0.00..42674.32 rows=38 width=50)
>

For one reason or the other, the planner things a sequential scan is the
best solution. Try turning off seq_scan before the query and see if it
changes the plan (set enable_seqscan off;).

I've seen this problem with sub queries and that usually solves it.

--
This E-mail is covered by the Electronic Communications Privacy Act, 18
U.S.C. 2510-2521 and is legally privileged.

This information is confidential information and is intended only for the
use of the individual or entity named above. If the reader of this message
is not the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited.