Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A84049FB446 for ; Tue, 3 Apr 2007 02:28:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 66337-07 for ; Tue, 3 Apr 2007 02:28:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 945229FB430 for ; Tue, 3 Apr 2007 02:28:05 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l335S3q0017932; Tue, 3 Apr 2007 01:28:03 -0400 (EDT) To: "Alex Deucher" cc: "PostgreSQL Performance" Subject: Re: postgres 7.4 vs 8.x redux: query plans In-reply-to: References: Comments: In-reply-to "Alex Deucher" message dated "Tue, 03 Apr 2007 01:09:49 -0400" Date: Tue, 03 Apr 2007 01:28:03 -0400 Message-ID: <17931.1175578083@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/15 X-Sequence-Number: 23892 "Alex Deucher" writes: > and here are the query plans referenced in my last email (apologies if > you get these twice, they didn't seem to go through the first time, > perhaps due to size?). I cut out the longer ones. The first case looks a whole lot like 8.2 does not think it can use an index for LIKE, which suggests strongly that you've used the wrong locale in the 8.2 installation (ie, not C). The second pair of plans may look a lot different but in principle they ought to perform pretty similarly. I think the performance differential may at root be that string comparison is way more expensive in the 8.2 installation, which again is possible if you went from C locale to some other locale. In short: check out "show lc_collate" in both installations. regards, tom lane