X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DB22FD1C98E for ; Fri, 6 Feb 2004 17:15:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 88406-09 for ; Fri, 6 Feb 2004 13:15:17 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id AEE77D1B51F for ; Fri, 6 Feb 2004 13:15:16 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 40128359B5; Fri, 6 Feb 2004 09:13:42 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3E846359B4; Fri, 6 Feb 2004 09:13:42 -0800 (PST) Date: Fri, 6 Feb 2004 09:13:42 -0800 (PST) From: Stephan Szabo To: Bruno BAGUETTE Cc: pgsql-performance@postgresql.org Subject: Re: RE : Increase performance of a UNION query that thakes In-Reply-To: Message-ID: <20040206085014.N88075@megazone.bigpanda.com> References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/95 X-Sequence-Number: 5619 On Fri, 6 Feb 2004, Bruno BAGUETTE wrote: > > In addition to what Tom said, the row estimates look > > suspiciously default. You mention vacuuming, but do you ever > > analyze the tables? > > I run VACUUM FULL ANALYZE with the postgres user on all the PostgreSQL > databases on the server, twice a day, sometimes more. Wierd, because you're getting 1000 estimated on both people and organizations. What does pg_class have to say about those two tables? > > Also, what do you have sort_mem set to? > > [root@levure data]# cat postgresql.conf | grep sort_mem > sort_mem = 6144 # min 64, size in KB > > Do you think I should increase that value ? Hmm, I'd expect that the sort would fit in that space in general. If you want to try different values, you can set sort_mem from psql rather than changing the configuration file. ---- On my machine the index does actually help, although I needed to lower random_page_cost a little from its default of 4 to get it to use it preferentially, but I'm also getting times about 1/3 of yours (and my machine is pretty poor) so I think I may not have data that matches yours very well.