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

Re: Help speeding up delete

From: Steve Wampler <swampler(at)noao(dot)edu>
To: Postgres-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Help speeding up delete
Date: 2005-11-15 00:28:19
Message-ID: 43792BA3.3060702@noao.edu (view raw or flat)
Thread:
Lists: pgsql-performancepgsql-www
Joshua Marsh wrote:
> 
> 
> On 11/14/05, *Steve Wampler* <swampler(at)noao(dot)edu
> <mailto: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.
> 

Hmmm, not only does it still use sequential scans, it thinks it'll take
even longer:

   set enable_seqscan to off;
   SET VARIABLE
   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=100000000.00..160237039405992.50 rows=800836 width=6)
     SubPlan
       ->  Materialize  (cost=100043604.06..100043604.06 rows=45 width=26)
             ->  Seq Scan on tmp_table2  (cost=100000000.00..100043604.06 rows=45 width=26)

   EXPLAIN

But the advice sounds like it *should* have helped...

-- 
Steve Wampler -- swampler(at)noao(dot)edu
The gods that smiled on your birth are now laughing out loud.

In response to

pgsql-performance by date

Next:From: Scott LambDate: 2005-11-15 01:08:03
Subject: Re: Help speeding up delete
Previous:From: Joshua MarshDate: 2005-11-15 00:10:14
Subject: Re: Help speeding up delete

pgsql-www by date

Next:From: Scott LambDate: 2005-11-15 01:08:03
Subject: Re: Help speeding up delete
Previous:From: Joshua MarshDate: 2005-11-15 00:10:14
Subject: Re: Help speeding up delete

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