X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 484FBB4388E for ; Mon, 7 Jul 2003 14:40:45 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 00837-09 for ; Mon, 7 Jul 2003 11:40:34 -0300 (ADT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id 93678B4388B for ; Mon, 7 Jul 2003 11:40:33 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 19ZXAM-000JPM-0V; Mon, 07 Jul 2003 15:40:31 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 9175D179CD; Mon, 7 Jul 2003 15:40:28 +0100 (BST) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id EA82F1716D; Mon, 7 Jul 2003 15:40:27 +0100 (BST) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: Jeff Boes , pgsql-performance@postgresql.org Subject: Re: Optimizer differences between 7.2 and 7.3 Date: Mon, 7 Jul 2003 15:40:30 +0100 User-Agent: KMail/1.4.3 References: <1057587455.6466.26.camel@takin.private.nexcerpt.com> In-Reply-To: <1057587455.6466.26.camel@takin.private.nexcerpt.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200307071540.30041.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/130 X-Sequence-Number: 2423 On Monday 07 Jul 2003 3:17 pm, Jeff Boes wrote: > Our production database is running under 7.2.4; our test database > running almost the same data is at 7.3.3. One table has about 400,000 > rows in each schema. A query against an indexed column uses an index > scan under 7.2.4, but a sequential scan under 7.3.3. A count of the > table in question shows that they have comparable numbers of matching > rows. [snip[ > select count(*) from articles > where path_base like 'http://news.findlaw.com/hdocs%'; > count > ------- > 38 [snip] > I can't find any differences between the indexes (ix_articles_3 exists > in both schemas); the column statistics are set up the same (the > default); and the optimizer settings (costs in postgresql.conf) are the > same. Check the locale the database was initdb'd to. You'll probably find 7.2.4 i= s=20 in the "C" locale whereas 7.3.3 isn't. The "like" comparison can only use= =20 indexes in the "C" locale. I believe you might need to initdb again to fix= =20 this. --=20 Richard Huxton