X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 377E432A014 for ; Thu, 21 Oct 2004 16:14:31 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 53130-06 for ; Thu, 21 Oct 2004 15:14:21 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.202]) by svr1.postgresql.org (Postfix) with ESMTP id 916BA329F45 for ; Thu, 21 Oct 2004 16:14:17 +0100 (BST) Received: by rproxy.gmail.com with SMTP id 73so37559rnk for ; Thu, 21 Oct 2004 08:14:14 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=nzdYqxeVZqXLTp49IeT5waWE7IqaiMcSXaEWBbhi3+l442omzMhES7SDZd5u3IixvlD9uixNOw82duTkyKzvOwtYAdm7MmN9pynAr5eGnaBTVc4cu/507mSIMv7r4hRCjKAHfV347BP34uFxSTR3I2p0pMFX4pKInBZ/sP77/6M= Received: by 10.38.181.77 with SMTP id d77mr3130083rnf; Thu, 21 Oct 2004 08:14:14 -0700 (PDT) Received: by 10.38.151.75 with HTTP; Thu, 21 Oct 2004 08:14:14 -0700 (PDT) Message-ID: <157f648404102108141836a5ac@mail.gmail.com> Date: Thu, 21 Oct 2004 11:14:14 -0400 From: Aaron Werman Reply-To: Aaron Werman To: Victor Ciurus Subject: Re: Simple machine-killing query! Cc: pgsql-performance@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/356 X-Sequence-Number: 8832 Sounds like you need some way to match a subset of the data first, rather than try indices that are bigger than the data. Can you add operation indices, perhaps on the first 10 bytes of the keys in both tables or on a integer hash of all of the strings? If so you could join on the exact set difference over the set difference of the operation match. /Aaron On Thu, 21 Oct 2004 17:34:17 +0300, Victor Ciurus wrote: > Hi all, > > I'm writing this because I've reached the limit of my imagination and > patience! So here is it... > > 2 tables: > 1 containing 27 million variable lenght, alpha-numeric records > (strings) in 1 (one) field. (10 - 145 char lenght per record) > 1 containing 2.5 million variable lenght, alpha-numeric records > (strings) in 1 (one) field. > > table wehere created using: > CREATE TABLE "public"."BIGMA" ("string" VARCHAR(255) NOT NULL) WITH OIDS; + > CREATE INDEX "BIGMA_INDEX" ON "public"."BIGMA" USING btree ("string"); > and > CREATE TABLE "public"."DIRTY" ("string" VARCHAR(128) NOT NULL) WITH OIDS; + > CREATE INDEX "DIRTY_INDEX" ON "public"."DIRTY" USING btree ("string"); > > What I am requested to do is to keep all records from 'BIGMA' that do > not apear in 'DIRTY' > So far I have tried solving this by going for: > > [explain] select * from BIGMA where string not in (select * from DIRTY); > QUERY PLAN > ------------------------------------------------------------------------ > Seq Scan on bigma (cost=0.00..24582291.25 rows=500 width=145) > Filter: (NOT (subplan)) > SubPlan > -> Seq Scan on dirty (cost=0.00..42904.63 rows=2503963 width=82) > (4 rows) > > AND > > [explain] select * from bigma,dirty where bigma.email!=dirty.email; > QUERY PLAN > ----------------------------------------------------------------------- > Nested Loop (cost=20.00..56382092.13 rows=2491443185 width=227) > Join Filter: (("inner".email)::text <> ("outer".email)::text) > -> Seq Scan on dirty (cost=0.00..42904.63 rows=2503963 width=82) > -> Materialize (cost=20.00..30.00 rows=1000 width=145) > -> Seq Scan on bigma (cost=0.00..20.00 rows=1000 width=145) > (5 rows) > > Now the problem is that both of my previous tries seem to last > forever! I'm not a pqsql guru so that's why I'm asking you fellas to > guide mw right! I've tried this on mysql previosly but there seems to > be no way mysql can handle this large query. > > QUESTIONS: > What can I do in order to make this work? > Where do I make mistakes? Is there a way I can improve the performance > in table design, query style, server setting so that I can get this > monster going and producing a result? > > Thanks all for your preciuos time and answers! > > Victor C. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Regards, /Aaron