X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 8F50C9FB318 for ; Tue, 15 Aug 2006 11:17:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 36091-08 for ; Tue, 15 Aug 2006 14:16:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 25E079FB2DB for ; Tue, 15 Aug 2006 11:16:57 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Tue, 15 Aug 2006 14:16:56 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 15 Aug 2006 09:16:56 -0500 Subject: Re: Breaking up a query From: Scott Marlowe To: Saad Anis Cc: pgsql-sql@postgresql.org In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1155651416.20252.181.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 15 Aug 2006 09:16:56 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=SPF_PASS, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200608/109 X-Sequence-Number: 26067 On Thu, 2006-08-10 at 17:53, Saad Anis wrote: > Hi Guys, > > A fellow at work has written the SQL below to retrieve some data from > multiple tables. Obviously it is inefficient and unnecessarily complex, and > I am trying to break it into 2 or more queries so as to enhance performance. Nope, that's not true in PostgreSQL. It is for some databases with relatively simplistic query planners, but not postgresql. I'd check that you have indexes where you need them (generally when you see a seq scan on a small set) including, especially, the foreign key columns (i.e. the ones pointing to another table's primary keys). On to your explain analyze, I noticed a lot of lines like this: Index Scan using positions_pkey on positions p (cost=0.00..32.00 rows=1000 width=28) (actual time=0.019..90.920 rows=13958 loop seeing as how the statistical default for a new, unanalyzed table is 1000, and you've got 1000 peppered all through your explain analyze, I'd guess you've not analyzed your database. Which means you've likely not read the admin docs. which means you've likely not vacuumed the database. Read the admin docs (they're not that thick, and there's lots of good info in there) and apply things like vacuum and analyze, and get back to us on how things are doing then.