Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5MLBda94762 for ; Fri, 22 Jun 2001 17:11:39 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f5MLBV305813; Fri, 22 Jun 2001 17:11:31 -0400 (EDT) To: "Josh Berkus" cc: pgsql-sql@postgresql.org Subject: Re: View performance question In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Fri, 22 Jun 2001 11:37:44 -0700" Date: Fri, 22 Jun 2001 17:11:31 -0400 Message-ID: <5809.993244291@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/390 X-Sequence-Number: 3687 "Josh Berkus" writes: > I tried re-arranging the JOINS as you suggested. There was no impact on > gross performance (still 42 seconds to return the first row). And yes, > it is the classic "star" database. > What follows is the query plan. Hm. The query plan looks very reasonable: hashing the smaller tables is exactly what I'd think it should do. If the rows estimates shown in the plan are accurate, it's hard to see how it's spending 42 seconds on this. The primary tables seem to be big enough that they are probably being divided into hash batches, if you use the default sort_mem setting of 512K. Try increasing sort_mem (SET SORT_MEM TO 5000 or so) and see if that makes a difference. regards, tom lane