X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E65FCD1D59B for ; Mon, 1 Dec 2003 19:48:50 +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 69074-01 for ; Mon, 1 Dec 2003 15:48:21 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id C4AE1D1BC54 for ; Mon, 1 Dec 2003 15:48:19 -0400 (AST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 4006945; Mon, 01 Dec 2003 11:49:10 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Jeremiah Jahn , Hannu Krosing Subject: Re: cross table indexes or something? Date: Mon, 1 Dec 2003 11:47:51 -0800 User-Agent: KMail/1.4.3 Cc: postgres performance References: <1069877651.22346.13.camel@bluejay.goodinassociates.com> <1069885949.3176.57.camel@fuji.krosing.net> <1070288942.22346.27.camel@bluejay.goodinassociates.com> In-Reply-To: <1070288942.22346.27.camel@bluejay.goodinassociates.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200312011147.51359.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/15 X-Sequence-Number: 4875 Jeremiah, > I've attached the Analyze below. I have no idea why the db thinks there > is only 1 judge named simth. Is there some what I can inform the DB > about this. In actuality, there aren't any judges named smith at the > moment, but there are 22K people named smith. No, Hannu meant that you may need to run the following command: ANALYZE actor; ... to update the database statistics on the actors table. That is a maintainence task that needs to be run periodically. If that doesn't fix the bad plan, then the granularity of statistics on the full_name column needs updating; I suggest: ALTER TABLE actor ALTER COLUMN full_name SET STATISTICS 100; ANALYZE actor; And if it's still choosing a slow nested loop, up the stats to 250. -- Josh Berkus Aglio Database Solutions San Francisco