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 BCEE2D1B2BD for ; Tue, 3 Aug 2004 05:40:57 -0300 (ADT) 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 65355-04 for ; Tue, 3 Aug 2004 08:40:51 +0000 (GMT) Received: from mta2.rdslink.ro (mta2.rdslink.ro [193.231.236.124]) by svr1.postgresql.org (Postfix) with ESMTP id 156B3D1B2D9 for ; Tue, 3 Aug 2004 05:40:48 -0300 (ADT) Received: (qmail 22001 invoked from network); 3 Aug 2004 11:35:38 -0000 Received: from unknown (HELO mail.rdslink.ro) (193.231.236.20) by mta2.rdslink.ro with DES-CBC3-SHA encrypted SMTP; 3 Aug 2004 11:35:38 -0000 Received: (qmail 15830 invoked from network); 3 Aug 2004 08:39:58 -0000 Received: from unknown (HELO ?81.196.83.211?) (81.196.83.211) by mail.rdslink.ro with SMTP; 3 Aug 2004 08:39:58 -0000 Subject: NOT IN query takes forever From: Marius Andreiana To: pgsql-performance@postgresql.org Content-Type: text/plain Organization: Galuna S.R.L. Date: Tue, 03 Aug 2004 11:49:02 +0300 Message-Id: <1091522942.4936.5.camel@marte.biciclete.ro> Mime-Version: 1.0 X-Mailer: Evolution 1.5.9.1 (1.5.9.1-2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200408/26 X-Sequence-Number: 7662 Hi I have 2 tables like this: CREATE TABLE query ( query_id int not null, dat varchar(64) null , sub_acc_id int null , query_ip varchar(64) null , osd_user_type varchar(64) null ) ; CREATE TABLE trans ( transaction_id varchar(64) not null , date varchar(64) null , query_id int not null , sub_acc_id int null , reg_acc_id int null ) ; CREATE UNIQUE INDEX query_query_id_idx ON query (query_id) ; CREATE INDEX trans_reg_acc_id_idx ON trans (reg_acc_id) ; CREATE INDEX trans_query_id_idx ON trans(query_id) ; osd=> select count(*) from trans osd-> ; count -------- 598809 (1 row) osd=> osd=> select count(*) from query osd-> ; count -------- 137042 (1 row) I just vacuum analyse'd the database. Trying to run this query: EXPLAIN ANALYSE select * FROM trans WHERE query_id NOT IN (select query_id FROM query) but it will remain like that forever (cancelled after 30 min). My postgresql.conf is the default: # - Memory - shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each #sort_mem = 1024 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB Should I adjust something? Using postgresql 7.4.2, saw in release notes that IN/NOT IN queries are at least as faster than EXISTS. Thank you! -- Marius Andreiana Galuna - Solutii Linux in Romania http://www.galuna.ro