Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id A6CB1633B3C
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  9 Jul 2009 14:55:30 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 97991-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  9 Jul 2009 14:55:17 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail1.mailhostingservice.com (mail1.mailhostingservice.com
	[216.154.198.171])
	by mail.postgresql.org (Postfix) with SMTP id 552846347AC
	for <pgsql-sql@postgresql.org>; Thu,  9 Jul 2009 14:53:18 -0300 (ADT)
Received: From aers.ca (24.68.32.77) by mail1.mailhostingservice.com Id
	<2cd045c7d2e53733c70ea4c40e6c697e581781cb@mail1.mailhostingservice.com>
	; Thr, 9 Jul 2009 17:52:13 +0000
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: Re: FW: Query length limitation in postgres server > 8.2.9
Date: Thu, 9 Jul 2009 10:53:14 -0700
Message-ID: <6B5AF6293A289F45826220B17ABE7937FDB1C0@BORON.aers.local>
In-Reply-To: <6B5AF6293A289F45826220B17ABE7937FDB16C@BORON.aers.local>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [SQL] FW: Query length limitation in postgres server > 8.2.9
Thread-Index: Acn/Toj3+uQvGLS7R4iTLUaooYhk/wAA2rGAAFrpBBA=
References: <6B5AF6293A289F45826220B17ABE7937FDB16C@BORON.aers.local>
From: <jacob@aers.ca>
To: <pgsql-sql@postgresql.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL=0.000,
	UNPARSEABLE_RELAY=0.001
X-Spam-Level: 
X-Archive-Number: 200907/40
X-Sequence-Number: 32781

I've simplified the query to make it easier to look at.

This one doesn't use the index's and therefore takes about 11713ms to =
return.

EXPLAIN ANALYZE SELECT * FROM MyTable1 where (MyColumn1 IN
(4305,112798,112799,112800,112801,112802,112803,112804,112770,112771,1127=
72,112773,112774,112775,
112776,112777,112778,112779,112780,112781,112782,112783,112784,112785,112=
786,3641,3719,72489,72490,
90843,93785,101650,91506,27252,101651,38616,72491,101652,11055,11056,2725=
3,72492,11058,11059,1489,
11321,72493,72494,90844,93786,101653,91507,27255,101654,38595,72496,11323=
,11324,27256,72495,11326,
11327,16157,117562,127748,127749,127750,127751,127752,127753,127754,12775=
5,127756,127757,127758,
127759,127760,127761,127762,127763,91514,123436,20386,27184,68263,90850,9=
3781,91525,27186,101646,
38603,68264,27187,27188,27190,27189,27191,27192,38604,112847,62053));

This one uses the index's and therefore takes about 2.5ms to return.

EXPLAIN ANALYZE SELECT * FROM MyTable1 where (MyColumn1 IN
(4305,112798,112799,112800,112801,112802,112803,112804,112770,112771,1127=
72,112773,112774,112775,
112776,112777,112778,112779,112780,112781,112782,112783,112784,112785,112=
786,3641,3719,72489,72490,
90843,93785,101650,91506,27252,101651,38616,72491,101652,11055,11056,2725=
3,72492,11058,11059,1489,
11321,72493,72494,90844,93786,101653,91507,27255,101654,38595,72496,11323=
,11324,27256,72495,11326,
11327,16157,117562,127748,127749,127750,127751,127752,127753,127754,12775=
5,127756,127757,127758,
127759,127760,127761,127762,127763,91514,123436,20386,27184,68263,90850,9=
3781,91525,27186,101646,
38603,68264,27187,27188,27190,27189,27191,27192,38604,112847));

Both work in 8.2.9. The only difference is the second version is =
restricted to 100 items in the IN statement where the first version has =
101 items.

-----Original Message-----
From: pgsql-sql-owner@postgresql.org =
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of jacob@aers.ca
Sent: Tuesday, July 07, 2009 3:34 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] FW: Query length limitation in postgres server > 8.2.9

One of our programmers has come to me with a problem. On 3 new Centos =
5.3 servers running Postgres 8.2.13 query's are taking 3500ms-5000ms to =
complete, where the same query on an older server (same hardware, older =
software revisions) the same query on the same data comes back in < 50 =
ms. After some investigation it seems that the new server is refusing to =
use the index's but if I limit the number of arguments in the latter =
part of the statement to 100 then it works as expected in the expected =
amount of time using the indexs. Rebuilding the index's in 8.2.13 had no =
effect.

The Query
EXPLAIN ANALYZE SELECT
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 (CASE
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHEN age < 18 THEN '_18'
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHEN age >=3D 18 and age <=3D =
25 THEN '18_25'
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHEN age >=3D 26 and age <=3D =
35 THEN '26_35'=20
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHEN age >=3D 36 and age <=3D =
50 THEN '36_50'
=A0=A0=A0=A0=A0=A0=A0 =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0WHEN age >=3D 51 and age <=3D 75 THEN '51_75'
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHEN age > 75 THEN '76_'
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ELSE 'Unspecified'
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 END) as ageRange,
=A0=A0=A0=A0=A0=A0=A0 =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0g=
ender,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 sum (current_price_usd * qty_sold) as revenue,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 sum(qty_avail) as available,
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 sum(qty_sold) as sold=A0 FROM search_site1_2009_03_13
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 WHERE buyer_cntry_id =3D 3
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
AND site_id =3D 1 AND (leaf_category_1 IN
(101658,112734,112735,112736,112737,112738,112739,112740,112741,112742,11=
2743,112744,112745,112746,112747,112748,
112749,112750,3582,72471,72472,90840,93782,91503,27194,101647,72473,72474=
,11035,11036,27195,72475,11038,11039,
38589,91510,112716,112717,112718,112719,112720,112721,112722,112723,11272=
4,112725,112726,112727,112728,112729,
112730,112731,112732,112698,112699,112700,112701,112702,112703,112704,112=
705,112706,112707,112708,112709,112710,
112711,112712,112713,112714,112680,112681,112682,112683,112684,112685,112=
686,112687,112688,112689,112690,112691,
112692,112693,112694,112695,112696,20390,72465,72466,90841,93783,91504,27=
219,101648,38581,72467,27220,27221,27223,
72468,27224,27225,38582,145519,112752,112768,112753,112754,112755,112756,=
112757,112758,112759,112760,112761,112762,
112763,112764,112765,112766,112767,11047,11048,38590,68257,27232,90847,93=
787,101656,91511,27233,101657,38591,38592,
101655,11049,11050,27234,11051,68250,68251,41070,41083,68252,41072,41073,=
68253,68254,68255,68256,11052,11053,3606,
4305,72483,72484,90842,93784,91505,27236,101649,38594,72486,15112,15113,2=
7237,72485,15115,15116,38614,112806,112807,
112808,112809,112810,112811,112812,112813,112814,112815,112816,112817,112=
818,112819,112820,112821,112822,49227,112788,
112789,112790,112791,112792,112793,112794,112795,112796,112797,112798,112=
799,112800,112801,112802,112803,112804,
112770,112771,112772,112773,112774,112775,112776,112777,112778,112779,112=
780,112781,112782,112783,112784,112785,
112786,3641,3719,72489,72490,90843,93785,101650,91506,27252,101651,38616,=
72491,101652,11055,11056,27253,72492,
11058,11059,1489,11321,72493,72494,90844,93786,101653,91507,27255,101654,=
38595,72496,11323,11324,27256,72495,11326,
11327,16157,117562,127748,127749,127750,127751,127752,127753,127754,12775=
5,127756,127757,127758,127759,127760,127761,
127762,127763,91514,123436,20386,27184,68263,90850,93781,91525,27186,1016=
46,38603,68264,27187,27188,27190,27189,
27191,27192,38604,112847,62053) AND Attribute_Value_02 & 2 > 0) GROUP BY =
ageRange, gender;

=A0
The Result
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0QUERY =
PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-----------------------------------------------------------
=A0HashAggregate=A0 (cost=3D2926874.53..2926874.58 rows=3D1 width=3D47) =
(actual time=3D3328.732..3328.732 rows=3D0 loops=3D1)
=A0=A0 ->=A0 Seq Scan on search_site1_2009_03_13=A0 =
(cost=3D0.00..2926873.97 rows=3D45 width=3D47) (actual =
time=3D3328.728..3328.728 rows=3D0 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0 Filter: ((buyer_cntry_id =3D 3) AND (site_id =
=3D 1) AND (leaf_category_1 =3D ANY =
('{101658,112734,112735,112736,112737,112738,112739,112740,112741,112742,=
112743,112744,112745,112746,112747,112748,112749,112750,3582,72471,72472,=
90840,93782,91503,27194,101647,72473,72474,11035,11036,27195,72475,11038,=
11039,38589,91510,112716,112717,112718,112719,112720,112721,112722,112723=
,112724,112725,112726,112727,112728,112729,112730,112731,112732,112698,11=
2699,112700,112701,112702,112703,112704,112705,112706,112707,112708,11270=
9,112710,112711,112712,112713,112714,112680,112681,112682,112683,112684,1=
12685,112686,112687,112688,112689,112690,112691,112692,112693,112694,1126=
95,112696,20390,72465,72466,90841,93783,91504,27219,101648,38581,72467,27=
220,27221,27223,72468,27224,27225,38582,145519,112752,112768,112753,11275=
4,112755,112756,112757,112758,112759,112760,112761,112762,112763,112764,1=
12765,112766,112767,11047,11048,38590,68257,27232,90847,93787,101656,9151=
1,27233,101657,38591,38592,101655,11049,11050,27234,11051,68250,68251,410=
70,41083,68252,41072,41073,68253,68254,68255,68256,11052,11053,3606,4305,=
72483,72484,90842,93784,91505,27236,101649,38594,72486,15112,15113,27237,=
72485,15115,15116,38614,112806,112807,112808,112809,112810,112811,112812,=
112813,112814,112815,112816,112817,112818,112819,112820,112821,112822,492=
27,112788,112789,112790,112791,112792,112793,112794,112795,112796,112797,=
112798,112799,112800,112801,112802,112803,112804,112770,112771,112772,112=
773,112774,112775,112776,112777,112778,112779,112780,112781,112782,112783=
,112784,112785,112786,3641,3719,72489,72490,90843,93785,101650,91506,2725=
2,101651,38616,72491,101652,11055,11056,27253,72492,11058,11059,1489,1132=
1,72493,72494,90844,93786,101653,91507,27255,101654,38595,72496,11323,113=
24,27256,72495,11326,11327,16157,117562,127748,127749,127750,127751,12775=
2,127753,127754,127755,127756,127757,127758,127759,127760,127761,127762,1=
27763,91514,123436,20386,27184,68263,90850,93781,91525,27186,101646,38603=
,68264,27187,27188,27190,27189,27191,27192,38604,112847,62053}'::integer[=
])) AND ((attribute_value_02 & 2::bigint) > 0))
=A0Total runtime: 3328.802 ms
(4 rows)


The truncated query returning in proper time and it's result:
EXPLAIN ANALYZE SELECT
(CASE
WHEN age < 18 THEN '_18'
WHEN age >=3D 18 and age <=3D 25 THEN '18_25'
WHEN age >=3D 26 and age <=3D 35 THEN '26_35'
WHEN age >=3D 36 and age <=3D 50 THEN '36_50'
WHEN age >=3D 51 and age <=3D 75 THEN '51_75'
WHEN age > 75 THEN '76_'
ELSE 'Unspecified'
END) as ageRange,
gender,
sum (current_price_usd * qty_sold) as revenue,
sum(qty_avail) as available,
sum(qty_sold) as sold FROM search_site1_2009_03_13
WHERE buyer_cntry_id =3D 3
AND site_id =3D 1 AND (leaf_category_1 IN
(4305,112798,112799,112800,112801,112802,112803,112804,112770,112771,1127=
72,112773,112774,112775,
112776,112777,112778,112779,112780,112781,112782,112783,112784,112785,112=
786,3641,3719,72489,72490,
90843,93785,101650,91506,27252,101651,38616,72491,101652,11055,11056,2725=
3,72492,11058,11059,1489,
11321,72493,72494,90844,93786,101653,91507,27255,101654,38595,72496,11323=
,11324,27256,72495,11326,
11327,16157,117562,127748,127749,127750,127751,127752,127753,127754,12775=
5,127756,127757,127758,
127759,127760,127761,127762,127763,91514,123436,20386,27184,68263,90850,9=
3781,91525,27186,101646,
38603,68264,27187,27188,27190,27189,27191,27192,38604,112847) AND =
Attribute_Value_02 & 2 > 0)=20
GROUP BY ageRange, gender;
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0QUERY =
PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0 =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
---
=A0HashAggregate=A0 (cost=3D44805.73..44805.78 rows=3D1 width=3D47) =
(actual time=3D0.966..0.966 rows=3D0 loops=3D1)
=A0=A0 ->=A0 Bitmap Heap Scan on search_site1_2009_03_13=A0 =
(cost=3D562.89..44805.44 rows=3D23 width=3D47) (actual =
time=3D0.965..0.965 rows=3D0 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0 Recheck Cond: ((leaf_category_1 =3D ANY =
('{4305,112798,112799,112800,112801,112802,112803,112804,112770,112771,11=
2772,112773,112774,112775,112776,112777,112778,112779,112780,112781,11278=
2,112783,112784,112785,112786,3641,3719,72489,72490,90843,93785,101650,91=
506,27252,101651,38616,72491,101652,11055,11056,27253,72492,11058,11059,1=
489,11321,72493,72494,90844,93786,101653,91507,27255,101654,38595,72496,1=
1323,11324,27256,72495,11326,11327,16157,117562,127748,127749,127750,1277=
51,127752,127753,127754,127755,127756,127757,127758,127759,127760,127761,=
127762,127763,91514,123436,20386,27184,68263,90850,93781,91525,27186,1016=
46,38603,68264,27187,27188,27190,27189,27191,27192,38604,112847}'::intege=
r[])) AND (site_id =3D 1))
=A0=A0=A0=A0=A0=A0=A0=A0 Filter: ((buyer_cntry_id =3D 3) AND =
((attribute_value_02 & 2::bigint) > 0))
=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Bitmap Index Scan on =
search_site1_2009_03_13_leaf_category_1=A0 (cost=3D0.00..562.88 =
rows=3D13630 width=3D0) (actual time=3D0.961..0.961 rows=3D0 loops=3D1)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ((leaf_category_1 =
=3D ANY =
('{4305,112798,112799,112800,112801,112802,112803,112804,112770,112771,11=
2772,112773,112774,112775,112776,112777,112778,112779,112780,112781,11278=
2,112783,112784,112785,112786,3641,3719,72489,72490,90843,93785,101650,91=
506,27252,101651,38616,72491,101652,11055,11056,27253,72492,11058,11059,1=
489,11321,72493,72494,90844,93786,101653,91507,27255,101654,38595,72496,1=
1323,11324,27256,72495,11326,11327,16157,117562,127748,127749,127750,1277=
51,127752,127753,127754,127755,127756,127757,127758,127759,127760,127761,=
127762,127763,91514,123436,20386,27184,68263,90850,93781,91525,27186,1016=
46,38603,68264,27187,27188,27190,27189,27191,27192,38604,112847}'::intege=
r[])) AND (site_id =3D 1))
=A0Total runtime: 1.048 ms
(7 rows)

Disabling sequence scan does nothing but increase cost values, I've =
downgraded just postgres on one of the 8.2.13 machines to 8.2.9, pointed =
it at the same PGDATA dir and it works fine again so it's not hardware =
or another part of the OS being a problem. I didn't change the config so =
it shouldn't be that either.=20

8.4.0 has the same results as 8.2.13.

I'm not sure where to go from here and would appreciate any idea's you =
guys and gals might have

config file contains:

listen_addresses =3D '*'          # what IP address(es) to listen on;
port =3D 5432
max_connections =3D 62
shared_buffers =3D 1000                   # min 16 or max_connections*2, =
8KB each
work_mem =3D 32768                        # min 64, size in KB
maintenance_work_mem =3D 32768            # min 1024, size in KB
max_fsm_pages =3D 120000                  # min max_fsm_relations*16, 6 =
bytes each
max_fsm_relations =3D 1000                # min 100, ~70 bytes each
fsync =3D false                           # turns forced synchronization =
on or off
full_page_writes =3D off                  # recover from partial page =
writes
effective_cache_size =3D 16384            # typically 8KB each
random_page_cost =3D 1                    # units are one sequential =
page fetch
geqo =3D off
default_statistics_target =3D 10          # range 1-1000
log_line_prefix =3D '%m'                  # Special values:
autovacuum =3D on                 # enable autovacuum subprocess?
datestyle =3D 'sql'
lc_messages =3D 'C'                       # locale for system error =
message
lc_monetary =3D 'C'                       # locale for monetary =
formatting
lc_numeric =3D 'C'                        # locale for number formatting
lc_time =3D 'C'                           # locale for time formatting

---------------------------------------------
=A0
Jacob Bresciani, Systems Administrator
Advanced E-commerce Research Systems Inc.
2307-4464 Markham Street
Victoria, BC
CANADA=A0 V8Z 7X8
+1 250 418 5412 (mobile)
+1 250 483 3271 (FAX)
www.terapeak.com - eBay Marketplace Research
www.aers.ca - Advanced E-commerce Analytics


--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql