Re: FW: Query length limitation in postgres server > 8.2.9

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,112772,112773,112774,112775,
112776,112777,112778,112779,112780,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,127755,127756,127757,127758,
127759,127760,127761,127762,127763,91514,123436,20386,27184,68263,90850,93781,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,112772,112773,112774,112775,
112776,112777,112778,112779,112780,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,127755,127756,127757,127758,
127759,127760,127761,127762,127763,91514,123436,20386,27184,68263,90850,93781,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
firstversion 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
taking3500ms-5000ms to complete, where the same query on an older server (same hardware, older software revisions) the
samequery on the same data comes back in < 50 ms. After some investigation it seems that the new server is refusing to
usethe index's but if I limit the number of arguments in the latter part of the statement to 100 then it works as
expectedin the expected amount of time using the indexs. Rebuilding the index's in 8.2.13 had no effect. 

The Query
EXPLAIN ANALYZE SELECT
                                (CASE
                                        WHEN age < 18 THEN '_18'
                                        WHEN age >= 18 and age <= 25 THEN '18_25'
                                        WHEN age >= 26 and age <= 35 THEN '26_35'
                                        WHEN age >= 36 and age <= 50 THEN '36_50'
                                        WHEN age >= 51 and age <= 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 = 3
                        AND site_id = 1 AND (leaf_category_1 IN
(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,112699,112700,112701,112702,112703,112704,112705,112706,112707,112708,112709,112710,
112711,112712,112713,112714,112680,112681,112682,112683,112684,112685,112686,112687,112688,112689,112690,112691,
112692,112693,112694,112695,112696,20390,72465,72466,90841,93783,91504,27219,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,93787,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,27237,72485,15115,15116,38614,112806,112807,
112808,112809,112810,112811,112812,112813,112814,112815,112816,112817,112818,112819,112820,112821,112822,49227,112788,
112789,112790,112791,112792,112793,112794,112795,112796,112797,112798,112799,112800,112801,112802,112803,112804,
112770,112771,112772,112773,112774,112775,112776,112777,112778,112779,112780,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,127755,127756,127757,127758,127759,127760,127761,
127762,127763,91514,123436,20386,27184,68263,90850,93781,91525,27186,101646,38603,68264,27187,27188,27190,27189,
27191,27192,38604,112847,62053) AND Attribute_Value_02 & 2 > 0) GROUP BY ageRange, gender;

 
The Result
                                                                                                        QUERY
PLAN                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=2926874.53..2926874.58 rows=1 width=47) (actual time=3328.732..3328.732 rows=0 loops=1)
   ->  Seq Scan on search_site1_2009_03_13  (cost=0.00..2926873.97 rows=45 width=47) (actual time=3328.728..3328.728
rows=0loops=1) 
         Filter: ((buyer_cntry_id = 3) AND (site_id = 1) AND (leaf_category_1 = 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,112699,112700,112701,112702,112703,112704,112705,112706,112707,112708,112709,112710,112711,112712,112713,112714,112680,112681,112682,112683,112684,112685,112686,112687,112688,112689,112690,112691,112692,112693,112694,112695,112696,20390,72465,72466,90841,93783,91504,27219,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,93787,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,27237,72485,15115,15116,38614,112806,112807,112808,112809,112810,112811,112812,112813,112814,112815,112816,112817,112818,112819,112820,112821,112822,49227,112788,112789,112790,112791,112792,112793,112794,112795,112796,112797,112798,112799,112800,112801,112802,112803,112804,112770,112771,112772,112773,112774,112775,112776,112777,112778,112779,112780,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,127755,127756,127757,127758,127759,127760,127761,127762,127763,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)) 
 Total 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 >= 18 and age <= 25 THEN '18_25'
WHEN age >= 26 and age <= 35 THEN '26_35'
WHEN age >= 36 and age <= 50 THEN '36_50'
WHEN age >= 51 and age <= 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 = 3
AND site_id = 1 AND (leaf_category_1 IN
(4305,112798,112799,112800,112801,112802,112803,112804,112770,112771,112772,112773,112774,112775,
112776,112777,112778,112779,112780,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,127755,127756,127757,127758,
127759,127760,127761,127762,127763,91514,123436,20386,27184,68263,90850,93781,91525,27186,101646,
38603,68264,27187,27188,27190,27189,27191,27192,38604,112847) AND Attribute_Value_02 & 2 > 0)
GROUP BY ageRange, gender;

                                                                                                                                                                      
                                                                                                                                                                                                  QUERY
PLAN                                                   
                                                                                                                                                                                                             

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=44805.73..44805.78 rows=1 width=47) (actual time=0.966..0.966 rows=0 loops=1)
   ->  Bitmap Heap Scan on search_site1_2009_03_13  (cost=562.89..44805.44 rows=23 width=47) (actual time=0.965..0.965
rows=0loops=1) 
         Recheck Cond: ((leaf_category_1 = ANY
('{4305,112798,112799,112800,112801,112802,112803,112804,112770,112771,112772,112773,112774,112775,112776,112777,112778,112779,112780,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,127755,127756,127757,127758,127759,127760,127761,127762,127763,91514,123436,20386,27184,68263,90850,93781,91525,27186,101646,38603,68264,27187,27188,27190,27189,27191,27192,38604,112847}'::integer[]))
AND(site_id = 1)) 
         Filter: ((buyer_cntry_id = 3) AND ((attribute_value_02 & 2::bigint) > 0))
         ->  Bitmap Index Scan on search_site1_2009_03_13_leaf_category_1  (cost=0.00..562.88 rows=13630 width=0)
(actualtime=0.961..0.961 rows=0 loops=1) 
               Index Cond: ((leaf_category_1 = ANY
('{4305,112798,112799,112800,112801,112802,112803,112804,112770,112771,112772,112773,112774,112775,112776,112777,112778,112779,112780,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,127755,127756,127757,127758,127759,127760,127761,127762,127763,91514,123436,20386,27184,68263,90850,93781,91525,27186,101646,38603,68264,27187,27188,27190,27189,27191,27192,38604,112847}'::integer[]))
AND(site_id = 1)) 
 Total 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
machinesto 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
OSbeing a problem. I didn't change the config so it shouldn't be that either.  

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

---------------------------------------------
 
Jacob Bresciani, Systems Administrator
Advanced E-commerce Research Systems Inc.
2307-4464 Markham Street
Victoria, BC
CANADA  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


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