Thread: 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
On Tue, Jul 7, 2009 at 11:33 PM, <jacob@aers.ca> wrote: > 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. Ugh, I thought this sounded familiar. I think you're hitting this limit which was put in place in 8.2.12 to protect against very slow planning times for very long IN lists: /** Proof attempts involving large arrays in ScalarArrayOpExpr nodes are* likely to require O(N^2) time, and more often thannot fail anyway.* So we set an arbitrary limit on the number of array elements that* we will allow to be treated as anAND or OR clause.* XXX is it worth exposing this as a GUC knob?*/ #define MAX_SAOP_ARRAY_SIZE 100 For your situation I'm not sure what to suggest. You could try to make the query more complex with something like WHERE site_id = 1 AND (leaf_category in (...) OR leaf_category IN (...)) but I'm not too hopeful that will work out well. I wonder if you couldn't get a better plan by stuffing these values into a temporary table (or even a VALUES query query) and doing a join. Offhand I don't see any great plan this would result in. One option would be to recompile postgres with this limit raised. Keep in mind that the long planning times it was meant to protect against might start to be a problem, but if you weren't already having a problem with that in <8.2.11 then perhaps you would be ok. -- greg http://mit.edu/~gsstark/resume.pdf
Greg Stark <gsstark@mit.edu> writes:
> On Tue, Jul 7, 2009 at 11:33 PM, <jacob@aers.ca> wrote:
>> 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.
> Ugh, I thought this sounded familiar. I think you're hitting this
> limit which was put in place in 8.2.12 to protect against very slow
> planning times for very long IN lists:
Nonsense ... unless perhaps the index is partial, and the IN condition
is relevant to proving it's legal to use the index? Given the lack of
any schema information in the complaint, I suppose I can't rule that
out, but one would hope the OP would have mentioned such a thing.
regards, tom lane
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
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql- > owner@postgresql.org] On Behalf Of jacob@aers.ca > Sent: Thursday, July 09, 2009 1:53 PM > > 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. Have you considered joining two tables, one of which is a UNION ALL sum of the identifiers? For example: Select * From MyTable Inner join ( Select 12345 as MyColumn Union all select 45678 Union all select 7890 ) as MyCriteria on MyTable.MyColumn = MyCriteria.MyColumn; Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital
<jacob@aers.ca> writes:
> I've simplified the query to make it easier to look at.
We need to see the table/index declarations. The query by itself is
just about useless.
regards, tom lane
\d search_site1_2009_03_13
Table "public.search_site1_2009_03_13" Column | Type | Modifiers
----------------------+------------------------+-----------item_id | bigint | not
nulltransaction_id | bigint | not nullsite_id | smallint | not
nullleaf_category_1 | integer | not nullcurrent_price_usd | numeric | not
nullbuy_it_now_price_usd| numeric |qty_sold | integer | not nullqty_avail
| integer | not nullauction_type | smallint | not nullflags |
integer | not nullseller_user_id | bigint | not nullstart_price_usd | numeric
| not nulllisting_end_time | time without time zone |days_since_sale | smallint
|duration | smallint |hour | smallint |bid_count |
smallint |buyer_id | bigint |ship_from | character varying
|ship_to | character varying |ship_cost | numeric |age |
smallint |gender | character(1) |buyer_cntry_id | smallint
|seller_cntry_id | smallint |selling_channel | smallint |transaction_site_id |
smallint |keyword_01_crc | integer |keyword_02_crc | integer
|keyword_03_crc | integer |keyword_04_crc | integer |keyword_05_crc |
integer |keyword_06_crc | integer |keyword_07_crc | integer
|keyword_08_crc | integer |keyword_09_crc | integer |keyword_10_crc |
integer |keyword_11_crc | integer |keyword_12_crc | integer
|keyword_13_crc | integer |keyword_14_crc | integer |keyword_15_crc |
integer |keyword_16_crc | integer |keyword_17_crc | integer
|keyword_18_crc | integer |keyword_19_crc | integer |attribute_value_01 |
bigint |attribute_value_02 | bigint |attribute_value_03 | bigint
|attribute_value_04 | bigint |attribute_value_05 | bigint |attribute_value_06 |
bigint |attribute_value_07 | bigint |attribute_value_08 | bigint
|attribute_value_09 | bigint |attribute_value_10 | bigint |upc_code |
bigint |isbn_code | character varying |mpn_code | character varying
|relist_parent_id | bigint |
Indexes: "search_site1_2009_03_13_isbn_code" btree (isbn_code, site_id) WHERE
isbn_code IS NOT NULL "search_site1_2009_03_13_keyword_01_crc" btree (keyword_01_crc,
site_id) WHERE keyword_01_crc IS NOT NULL "search_site1_2009_03_13_keyword_02_crc" btree (keyword_02_crc,
site_id) WHERE keyword_02_crc IS NOT NULL "search_site1_2009_03_13_keyword_03_crc" btree (keyword_03_crc,
site_id) WHERE keyword_03_crc IS NOT NULL "search_site1_2009_03_13_keyword_04_crc" btree (keyword_04_crc,
site_id) WHERE keyword_04_crc IS NOT NULL "search_site1_2009_03_13_keyword_05_crc" btree (keyword_05_crc,
site_id) WHERE keyword_05_crc IS NOT NULL "search_site1_2009_03_13_keyword_06_crc" btree (keyword_06_crc,
site_id) WHERE keyword_06_crc IS NOT NULL "search_site1_2009_03_13_keyword_07_crc" btree (keyword_07_crc,
site_id) WHERE keyword_07_crc IS NOT NULL "search_site1_2009_03_13_keyword_08_crc" btree (keyword_08_crc,
site_id) WHERE keyword_08_crc IS NOT NULL "search_site1_2009_03_13_keyword_09_crc" btree (keyword_09_crc,
site_id) WHERE keyword_09_crc IS NOT NULL "search_site1_2009_03_13_keyword_10_crc" btree (keyword_10_crc,
site_id) WHERE keyword_10_crc IS NOT NULL "search_site1_2009_03_13_keyword_11_crc" btree (keyword_11_crc,
site_id) WHERE keyword_11_crc IS NOT NULL "search_site1_2009_03_13_keyword_12_crc" btree (keyword_12_crc,
site_id) WHERE keyword_12_crc IS NOT NULL "search_site1_2009_03_13_keyword_13_crc" btree (keyword_13_crc,
site_id) WHERE keyword_13_crc IS NOT NULL "search_site1_2009_03_13_keyword_14_crc" btree (keyword_14_crc,
site_id) WHERE keyword_14_crc IS NOT NULL "search_site1_2009_03_13_keyword_15_crc" btree (keyword_15_crc,
site_id) WHERE keyword_15_crc IS NOT NULL "search_site1_2009_03_13_keyword_16_crc" btree (keyword_16_crc,
site_id) WHERE keyword_16_crc IS NOT NULL "search_site1_2009_03_13_keyword_17_crc" btree (keyword_17_crc,
site_id) WHERE keyword_17_crc IS NOT NULL "search_site1_2009_03_13_keyword_18_crc" btree (keyword_18_crc,
site_id) WHERE keyword_18_crc IS NOT NULL "search_site1_2009_03_13_keyword_19_crc" btree (keyword_19_crc,
site_id) WHERE keyword_19_crc IS NOT NULL "search_site1_2009_03_13_leaf_category_1" btree (leaf_category_1,
site_id) WHERE leaf_category_1 IS NOT NULL "search_site1_2009_03_13_seller_user_id" btree (seller_user_id,
site_id) WHERE seller_user_id IS NOT NULL "search_site1_2009_03_13_upc_code" btree (upc_code, site_id) WHERE
upc_code IS NOT NULL
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, July 09, 2009 11:06 AM
To: Jacob Bresciani
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] FW: Query length limitation in postgres server >
8.2.9
<jacob@aers.ca> writes:
> I've simplified the query to make it easier to look at.
We need to see the table/index declarations. The query by itself is
just about useless.
regards, tom lane
<jacob@aers.ca> writes:
> \d search_site1_2009_03_13
And "MyColumn1" is really which column?
regards, tom lane
Good think I obfuscated it in one place eh :)
leaf_category_1
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, July 09, 2009 11:14 AM
To: Jacob Bresciani
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] FW: Query length limitation in postgres server >
8.2.9
<jacob@aers.ca> writes:
> \d search_site1_2009_03_13
And "MyColumn1" is really which column?
regards, tom lane
Being just the server admin I'll forward this thought on to the dev's for consideration. -----Original Message----- From: Hartman, Matthew [mailto:Matthew.Hartman@krcc.on.ca] Sent: Thursday, July 09, 2009 10:58 AM To: Jacob Bresciani; pgsql-sql@postgresql.org Subject: RE: [SQL] FW: Query length limitation in postgres server > 8.2.9 > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql- > owner@postgresql.org] On Behalf Of jacob@aers.ca > Sent: Thursday, July 09, 2009 1:53 PM > > 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. Have you considered joining two tables, one of which is a UNION ALL sum of the identifiers? For example: Select * From MyTable Inner join ( Select 12345 as MyColumn Union all select 45678 Union all select 7890 ) as MyCriteria on MyTable.MyColumn = MyCriteria.MyColumn; Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital
<jacob@aers.ca> writes:
> leaf_category_1
Ah. So you are wishing it would use this index:
"search_site1_2009_03_13_leaf_category_1" btree (leaf_category_1,
site_id) WHERE leaf_category_1 IS NOT NULL
If I were you I'd drop the WHERE clause, which is eliminating no index
entries whatsoever (since the column is actually declared not null).
What it is doing is forcing the planner to expend many cycles proving
that the query's WHERE clause requires leaf_category_1 to be non-null
hence the index is potentially usable. The reason recent releases
are giving you trouble is that we put a limit on how many cycles we'd
expend on such silliness.
If you really don't want to change the schema, you could work around
the issue by adding a separate "leaf_category_1 IS NOT NULL" test to
the query, so that the planner can prove the index is relevant without
having to grovel through hundreds of IN-list items to do it.
In general, this table schema looks like somebody has drastically
overengineered the index definitions with rather little understanding
of what they were doing or what the performance consequences would be.
I hope the table is read-mostly, because you're paying a *lot* anytime
you update those indexes.
regards, tom lane
the data is insert once, read many so we should be fine on that side.
I've past this on to the dev's and I'll let you know when I get feed
back. Thanks for your input Tom (and the others).
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, July 09, 2009 11:29 AM
To: Jacob Bresciani
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] FW: Query length limitation in postgres server >
8.2.9
<jacob@aers.ca> writes:
> leaf_category_1
Ah. So you are wishing it would use this index:
"search_site1_2009_03_13_leaf_category_1" btree (leaf_category_1,
site_id) WHERE leaf_category_1 IS NOT NULL
If I were you I'd drop the WHERE clause, which is eliminating no index
entries whatsoever (since the column is actually declared not null).
What it is doing is forcing the planner to expend many cycles proving
that the query's WHERE clause requires leaf_category_1 to be non-null
hence the index is potentially usable. The reason recent releases
are giving you trouble is that we put a limit on how many cycles we'd
expend on such silliness.
If you really don't want to change the schema, you could work around
the issue by adding a separate "leaf_category_1 IS NOT NULL" test to
the query, so that the planner can prove the index is relevant without
having to grovel through hundreds of IN-list items to do it.
In general, this table schema looks like somebody has drastically
overengineered the index definitions with rather little understanding
of what they were doing or what the performance consequences would be.
I hope the table is read-mostly, because you're paying a *lot* anytime
you update those indexes.
regards, tom lane
<jacob@aers.ca> writes:
> the data is insert once, read many so we should be fine on that side.
It's not only the insert side where you pay for so many partial indexes.
On every query of the table, the planner is going to examine every one
of those indexes and determine whether the index is potentially usable.
Which means it attempts to prove the truth of the index WHERE clause
given the query WHERE conditions. Most of those proof attempts are going
to fail for most queries, but not before eating a lot of planning
cycles.
On a read-mostly table, it'd probably be better to just eat the index
space needed to index the NULL entries. They aren't going to affect
search speed noticeably for non-NULL entries, I think.
regards, tom lane