Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 028D79FA162 for ; Wed, 13 Dec 2006 18:08:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 78444-10 for ; Wed, 13 Dec 2006 18:08:46 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.optiosoftware.com (mail.optio.com [192.216.93.10]) by postgresql.org (Postfix) with ESMTP id 0F5DC9FA28E for ; Wed, 13 Dec 2006 18:08:45 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: strange query behavior Date: Wed, 13 Dec 2006 17:08:44 -0500 Message-ID: <47668A1334CDBF46927C1A0DFEB223D39F7A98@mail.optiosoftware.com> In-Reply-To: <12115.1166047135@sss.pgh.pa.us> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] strange query behavior Thread-Index: AccfAeMk5SR6jN7/SGimcosRgjfv2wAALWjQ References: <47668A1334CDBF46927C1A0DFEB223D39F79A5@mail.optiosoftware.com> <10648.1166037392@sss.pgh.pa.us> <47668A1334CDBF46927C1A0DFEB223D39F79FA@mail.optiosoftware.com> <12115.1166047135@sss.pgh.pa.us> From: "Tim Jones" To: "Tom Lane" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/343 X-Sequence-Number: 22399 Version 8.1 Here are the planner constraints I believe we changed effective_cache_size and random_page_cost BTW this is an AIX 5.2=20 #----------------------------------------------------------------------- ---- # QUERY TUNING #----------------------------------------------------------------------- ---- # - Planner Method Configuration - #enable_bitmapscan =3D on #enable_hashagg =3D on #enable_hashjoin =3D on #enable_indexscan =3D on #enable_mergejoin =3D on #enable_nestloop =3D on #enable_seqscan =3D on #enable_sort =3D on #enable_tidscan =3D on # - Planner Cost Constants - #effective_cache_size =3D 10000 # typically 8KB each effective_cache_size =3D 400000 random_page_cost =3D 3.8 # units are one sequential page fetch # cost #cpu_tuple_cost =3D 0.01 # (same) #cpu_index_tuple_cost =3D 0.001 # (same) #cpu_operator_cost =3D 0.0025 # (same) # - Genetic Query Optimizer - #geqo =3D on #geqo_threshold =3D 12 #geqo_effort =3D 5 # range 1-10 #geqo_pool_size =3D 0 # selects default based on effort #geqo_generations =3D 0 # selects default based on effort #geqo_selection_bias =3D 2.0 # range 1.5-2.0 # - Other Planner Options - #default_statistics_target =3D 10 # range 1-1000 #constraint_exclusion =3D off #from_collapse_limit =3D 8 #join_collapse_limit =3D 8 # 1 disables collapsing of explicit # JOINs Thanks Tim Jones Healthcare Project Manager Optio Software, Inc. (770) 576-3555 -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Wednesday, December 13, 2006 4:59 PM To: Tim Jones Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] strange query behavior=20 "Tim Jones" writes: > The tables for theses queries are vacuumed and analyzed regularly. I=20 > just did an analyze to be sure and here are the results ... There's something pretty wacko about the choice of plan in the slow case --- I don't see why it'd not have used the same plan structure as for the IN case. It's coming up with a cost a lot higher than for the other, so it certainly knows this isn't a great plan ... Which PG version is this exactly? Are you running with any nondefault planner parameters? regards, tom lane