From: Steve Wampler <swampler@noao.edu>
To: Postgres-performance <pgsql-performance@postgresql.org>
Subject: Re: Help speeding up delete
Date: Mon, 14 Nov 2005 17:28:19 -0700
Message-ID: <43792BA3.3060702@noao.edu> (raw)
In-Reply-To: <38242de90511141610j621d9a02r52a1d6f4fc55cb55@mail.gmail.com>
References: <43790A99.9050603@noao.edu>
<5E90D296-FA4A-4E48-92B4-396AADE35EF2@slamb.org>
<43792355.1020501@noao.edu>
<38242de90511141610j621d9a02r52a1d6f4fc55cb55@mail.gmail.com>
Joshua Marsh wrote:
>
>
> On 11/14/05, *Steve Wampler* <swampler@noao.edu
> <mailto:swampler@noao.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@noao.edu
The gods that smiled on your birth are now laughing out loud.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: swampler@noao.edu
Subject: Re: Help speeding up delete
In-Reply-To: <43792BA3.3060702@noao.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox