From: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: eliminating records not in (select id ... so SLOW? |
Date: | 2008-08-01 07:12:13 |
Message-ID: | 20080801091213.74491d80@dawn.webthatworks.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, 31 Jul 2008 21:37:39 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> writes:
> > I'm doing something like:
> > delete from table1 where id not in (select id from table2).
> > table1 contains ~1M record table2 contains ~ 600K record and id
> > is unique.
> That's going to pretty much suck unless you've got work_mem set
> high enough to allow a "hashed subplan" plan --- which is likely
> to require tens of MB for this case, I don't recall exactly what
Thanks.
> the per-row overhead is. Experiment until EXPLAIN tells you it'll
> use a hashed subplan.
explain delete from catalog_categoryitem where ItemID not in (select
ItemID from catalog_items);
Well I reached 3Gb of work_mem and still I got:
"Seq Scan on catalog_categoryitem (cost=31747.84..4019284477.13
rows=475532 width=6)"
" Filter: (NOT (subplan))"
" SubPlan"
" -> Materialize (cost=31747.84..38509.51 rows=676167 width=8)"
" -> Seq Scan on catalog_items (cost=0.00..31071.67
rows=676167 width=8)"
I've this too:
alter table catalog_items cluster on catalog_items_pkey;
should I drop it?
This is just a dev box. I loaded the 2 tables with 2 not coherent
set of data just to play with, before adding all the pk/fk I need.
I could just truncate the tables and reload them from coherent
sources.
But what if I *really* had to execute that query?
Any other magic I could play to speed it up?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2008-08-01 08:21:11 | Re: Copy fails |
Previous Message | Erwin Brandstetter | 2008-08-01 04:06:00 | Re: Declaring constants in SQL |