Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2C80263405C for ; Thu, 5 Mar 2009 09:22:09 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 72388-09 for ; Thu, 5 Mar 2009 09:21:58 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.24]) by mail.postgresql.org (Postfix) with ESMTP id BDC42634031 for ; Thu, 5 Mar 2009 09:21:58 -0400 (AST) Received: by qw-out-2122.google.com with SMTP id 5so2972666qwd.61 for ; Thu, 05 Mar 2009 05:21:56 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=bSnY6YV+HB30htJWNQN4PY4Bq/474EvM391I1sGUdtY=; b=sf/0zQvP+rIoMapnOw5rz4DeD8R7fallCgI5Os0BHrB1fM7e4ROh9jTuL73i1gjsKc lTTE172V+Iu+NpMdAw1OxrMS5BsiAmqPk5LTJnqhVi10eGDC9m8j46HGzk35D1m5aLOz ett0BDLptIJnh4QdyrIWuL+q5tgKKs7mnkJHk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=TcBvuwe9B/8CknB+6zYtU+407cx/HWDhOCDwiVJDmoGQP7BNb89Q6jpzi9M+MMYzFP v8Stl7qQuGf8zomNjzzX5fS23bC6qn5+gRTyBijQFh6CiQkzDLiYy74mDxKYuZnBkTTU UDSZvUTPnubzGX9op/JpB5lhXyorMi15XZaFU= MIME-Version: 1.0 Received: by 10.229.110.16 with SMTP id l16mr628094qcp.92.1236259316658; Thu, 05 Mar 2009 05:21:56 -0800 (PST) In-Reply-To: References: <2870.1236050632@sss.pgh.pa.us> <17797.1236119652@sss.pgh.pa.us> Date: Thu, 5 Mar 2009 08:21:56 -0500 Message-ID: <603c8f070903050521q64ea85efnff129b1cb6d5c502@mail.gmail.com> Subject: Re: Postgres 8.3, four times slower queries? From: Robert Haas To: Aaron Guyon Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/49 X-Sequence-Number: 32971 On Wed, Mar 4, 2009 at 6:20 PM, Aaron Guyon wrote: > On Tue, Mar 3, 2009 at 5:34 PM, Tom Lane wrote: >> >> Comparisons on >> numerics aren't terribly fast though (in either release). =A0I wonder >> whether you could change the key columns to int or bigint. > > I changed the affected columns from numeric to integers and I was unable = to > get any performance gain: > 8.3.3: 1195 ms > 8.2.12: 611 ms > > I've attached the new query plans. > >> Are you doing >> something to force the join order, like running with a small >> join_collapse_limit setting? =A0If so maybe you shouldn't. > > No, we left the join_collapse_limit to the default 8.=A0 We tried a highe= r > value, but there was no difference in performance. > > I'll post the query and the table descriptions in separate messages to th= e > list to avoid my mail from being rejected for exceeding the size limit :) Well, it looks like the problem is that 8.3 is not using the index idx_bundle_content_bundle_id. But I don't know why that should be happening, unless there's a problem with that index. ...Robert