Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 852F0475F31 for ; Wed, 9 Oct 2002 06:32:12 -0400 (EDT) Received: from mailhub2.sghms.ac.uk (firewall.sghms.ac.uk [194.82.50.2]) by postgresql.org (Postfix) with ESMTP id 96233475D12 for ; Wed, 9 Oct 2002 06:32:11 -0400 (EDT) Received: from [194.82.51.24] (helo=imail) by mailhub2.sghms.ac.uk with esmtp (Exim 4.05) id 17zE5K-0000g7-00 for pgsql-sql@postgresql.org; Wed, 09 Oct 2002 11:28:58 +0100 Received: from [172.16.20.3] (mrc1-003.sghms.ac.uk [172.16.20.3]) by imail.sghms.ac.uk (iPlanet Messaging Server 5.1 HotFix 0.7 (built May 7 2002)) with ESMTPA id <0H3P00CRRMLM7J@imail.sghms.ac.uk> for pgsql-sql@postgresql.org; Wed, 09 Oct 2002 11:32:10 +0100 (BST) Date: Wed, 09 Oct 2002 11:32:18 +0100 From: Adam Witney In-reply-to: <3D9B3650.3040000@joeconway.com> To: pgsql-sql Message-id: MIME-version: 1.0 User-Agent: Microsoft-Entourage/10.1.0.2006 Subject: Complex SQL query and performance strategy Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT X-Spam-Status: No, hits=-102.9 required=5.5 tests=IN_REP_TO,TO_LOCALPART_EQ_REAL,USER_IN_WHITELIST version=2.31 X-Spam-Level: X-MailScanner: Found to be clean X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/122 X-Sequence-Number: 9839 Hi, I have a complex SQL query which requires the joining of 18 tables. There are only primary key indices on the table and at the moment it runs a little slow (30s or so) and so I am trying to optimise it. The output of EXPLAIN is a little confusing and seems to vary from run to run. Does the query optimiser have trouble with larger number of table joins? Also this will be running from a web front end, and I hope to have it encapsulated all within a function. Would it be better to break it up into multiple SQL statements/functions? Or to try to really tweak the indices? Thanks for any advice adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.