On Fri, 01 Aug 2008 10:33:59 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:
> > 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 (subp
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> Huh. The only way I can see for that to happen is if the datatypes
>> involved aren't hashable. What's the datatypes of the two columns
>> being compared, anyway?
> thanks to svn I'd say you're right... on
On Fri, 01 Aug 2008 10:33:59 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:
> > 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 (subp
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes:
> 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 wi
;
> Subject: Re: [GENERAL] eliminating records not in (select id ... so SLOW?
> To:
> Cc: "PostgreSQL"
> Date: Thursday, July 31, 2008, 11:01 PM
> On Thu, 31 Jul 2008 14:59:29 -0700 (PDT)
> Lennin Caro <[EMAIL PROTECTED]> wrote:
>
> > > The box is a 2x dua
Ivan Sergio Borgonovo wrote:
But what if I *really* had to execute that query?
Any other magic I could play to speed it up?
A trick that is sometimes spectacularly efficient is to rewrite the
query to use an outer join instead of NOT IN.
Try:
DELETE FROM table1 WHERE id IN
(SELECT
On Thu, 31 Jul 2008 21:37:39 -0400
Tom Lane <[EMAIL PROTECTED]> wrote:
> Ivan Sergio Borgonovo <[EMAIL PROTECTED]> 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
Ivan Sergio Borgonovo <[EMAIL PROTECTED]> 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
On Thu, 31 Jul 2008 14:59:29 -0700 (PDT)
Lennin Caro <[EMAIL PROTECTED]> wrote:
> > The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram.
> > Default debian etch setup.
> you recently run vacuum ?
The tables are pretty stable. I think no more than 20 records were
modified (update/insert/del
--- On Thu, 7/31/08, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote:
> From: Ivan Sergio Borgonovo <[EMAIL PROTECTED]>
> Subject: [GENERAL] eliminating records not in (select id ... so SLOW?
> To: "PostgreSQL"
> Date: Thursday, July 31, 2008, 9:45 PM
>
I'm doing something like:
delete from table1 where id not in (select id from table2).
both id are indexed.
table1 contains ~1M record table2 contains ~ 600K record and id is
unique.
The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram.
Default debian etch setup.
It has been working for ove
11 matches
Mail list logo
