X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9CF5B475F28 for ; Tue, 4 Mar 2003 01:27:48 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 78096-05 for ; Tue, 4 Mar 2003 01:27:44 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id DF0DE475F23 for ; Tue, 4 Mar 2003 01:27:43 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.8/8.12.8) with ESMTP id h246Rgx6020976; Tue, 4 Mar 2003 01:27:42 -0500 (EST) To: Alex Johnson Cc: pgsql-performance@postgresql.org Subject: Re: Slow performance with join on many fields In-reply-to: References: Comments: In-reply-to Alex Johnson message dated "Mon, 03 Mar 2003 21:48:49 -0800" Date: Tue, 04 Mar 2003 01:27:42 -0500 Message-ID: <20975.1046759262@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/3 X-Sequence-Number: 1301 Alex Johnson writes: > I'm looking at the following query that joins these three > tables: > SELECT ... > FROM > (tbl_samples > INNER JOIN tbl_tests USING ... > ) > INNER JOIN tbl_results USING ... You're forcing the join order; perhaps another order is preferable? See http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/explicit-joins.html > This is the output from EXPLAIN: EXPLAIN ANALYZE output would've been more useful (it would have shown whether a different join order would be better, for one thing). > I've done the following to try to improve performance: > increased shared_buffers to 384 That's on the picayune side yet. 1000 buffers or so is where you want to be, I think. Also, have you run ANALYZE or VACUUM ANALYZE lately? regards, tom lane