Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vemgs-0003O4-FE for pgsql-performance@arkaria.postgresql.org; Fri, 08 Nov 2013 14:05:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Vemgr-0001zh-QA for pgsql-performance@arkaria.postgresql.org; Fri, 08 Nov 2013 14:05:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vemgq-0001xh-5T for pgsql-performance@postgresql.org; Fri, 08 Nov 2013 14:05:36 +0000 Received: from speak.commontongue.com ([31.31.77.129] helo=vm6731.wedos.net) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vemgi-0001x1-N7 for pgsql-performance@postgresql.org; Fri, 08 Nov 2013 14:05:35 +0000 Received: from [10.0.0.57] (124.200.broadband4.iol.cz [85.71.200.124]) by vm6731.wedos.net (Postfix) with ESMTPSA id A27FB5F1CB for ; Fri, 8 Nov 2013 15:05:24 +0100 (CET) Message-ID: <527CEF87.6080008@commontongue.com> Date: Fri, 08 Nov 2013 15:04:55 +0100 From: Jan Walter User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.1.0 MIME-Version: 1.0 To: postgres performance list Subject: Size of IN list affects query plan Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.8 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Hi, I would like to know, how does the size of the IN list affect query planner. I have a query select distinct on (event_id, tag_id) et.id, e.id as event_id, t.id as tag_id, t.name, t.user_id, t.shared, t.color, case when ea.id <> e.id then true else false end as inherited from do_event e join do_event ea on (ea.tree_id =3D e.tree_id and ea.lft <=3D e.lft=20 and ea.rght >=3D e.rght) join do_event_tags et on (et.event_id =3D ea.id) join do_tag t on (t.id =3D et.tag_id) where e.id in (LIST_OF_INTEGERS) and (t.user_id =3D 14 or t.shared) order by event_id, tag_id, inherited; and have doubts, if the size of the list does not impact the plan=20 significantly. If LIST_OF_INTEGERS has <=3D233 values, the query is really fast: Unique (cost=3D2351.85..2353.71 rows=3D249 width=3D33) (actual=20 time=3D24.515..24.654 rows=3D163 loops=3D1) -> Sort (cost=3D2351.85..2352.47 rows=3D249 width=3D33) (actual=20 time=3D24.513..24.549 rows=3D166 loops=3D1) Sort Key: e.id, t.id, (CASE WHEN (ea.id <> e.id) THEN true=20 ELSE false END) Sort Method: quicksort Memory: 37kB -> Hash Join (cost=3D2217.89..2341.94 rows=3D249 width=3D33)=20 (actual time=3D18.987..24.329 rows=3D166 loops=3D1) Hash Cond: (et.event_id =3D ea.id) -> Hash Join (cost=3D4.73..119.62 rows=3D1612 width=3D29)= =20 (actual time=3D0.151..4.634 rows=3D2312 loops=3D1) Hash Cond: (et.tag_id =3D t.id) -> Seq Scan on do_event_tags et (cost=3D0.00..79.47= =20 rows=3D5147 width=3D12) (actual time=3D0.006..1.531 rows=3D5147 loops=3D1) -> Hash (cost=3D4.08..4.08 rows=3D52 width=3D21)=20 (actual time=3D0.119..0.119 rows=3D49 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 3kB -> Seq Scan on do_tag t (cost=3D0.00..4.08=20 rows=3D52 width=3D21) (actual time=3D0.019..0.097 rows=3D49 loops=3D1) Filter: ((user_id =3D 14) OR shared) -> Hash (cost=3D2157.26..2157.26 rows=3D4472 width=3D8)=20 (actual time=3D18.782..18.782 rows=3D270 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 11kB -> Nested Loop (cost=3D428.35..2157.26 rows=3D4472= =20 width=3D8) (actual time=3D0.597..18.595 rows=3D270 loops=3D1) Join Filter: ((ea.lft <=3D e.lft) AND (ea.rght= =20 >=3D e.rght)) -> Bitmap Heap Scan on do_event e=20 (cost=3D428.35..926.22 rows=3D232 width=3D16) (actual time=3D0.568..0.895=20 rows=3D233 loops=3D1) Recheck Cond: (id =3D ANY=20 ('{110364,110377,42337,1503,5490,106267,106607,108419,108836,108556,108744,= 108466,108467,106331,3717,105404,35179,3398,5675,5896,5888,5287,4679,4275,4= 042,1599,4041,3311,1588,1605,1607,1606,1604,1594,1850,110494,110041,107955,= 110373,110068,110114,109503,109925,108959,108964,109189,109598,109142,10930= 4,109607,107902,106668,109121,109101,109056,4621,109031,2574,5092,1674,1064= 52,108901,108849,108713,108783,108766,108386,108455,2560,108397,1538,2007,1= 08000,108389,108336,108456,36796,28985,108003,108421,108399,4871,106884,637= 1,36026,108204,108022,107941,107967,107911,107928,47944,107010,106640,10703= 7,106994,107011,55313,105862,106332,106498,5850,13369,106161,5859,28465,106= 385,106444,102751,106371,105131,2610,102753,4833,4936,4755,4699,105402,1408= 7,4798,4942,36249,55513,75790,75789,4238,6370,5744,5745,5149,4731,42297,348= 41,31190,17339,31155,31242,17701,17642,31203,31218,31376,5856,5141,18154,27= 146,17590,17566,13692,4867,1842,6365,6354,5480,5481,4382,5893,6355,5907,588= 6,5826,5028,4665,5230,5482,5273,4181,5091,4869,4983,4968,4961,4905,4906,403= 6,1483,4284,4790,4348,4648,4655,4647,4656,3075,4596,2144,4274,4592,4506,454= 9,4595,4188,4548,4511,4333,4306,4291,4240,4268,4114,3665,3547,1563,2102,151= 4,3579,3607,3501,2834,2436,3069,1400,2359,3056,3173,2897,2837,2780,2137,144= 7,1280,421,412,2076,1200,1691,446,1444,399,374,444,419,449}'::integer[])) -> Bitmap Index Scan on=20 do_event_pkey (cost=3D0.00..428.29 rows=3D232 width=3D0) (actual=20 time=3D0.538..0.538 rows=3D233 loops=3D1) Index Cond: (id =3D ANY=20 ('{110364,110377,42337,1503,5490,106267,106607,108419,108836,108556,108744,= 108466,108467,106331,3717,105404,35179,3398,5675,5896,5888,5287,4679,4275,4= 042,1599,4041,3311,1588,1605,1607,1606,1604,1594,1850,110494,110041,107955,= 110373,110068,110114,109503,109925,108959,108964,109189,109598,109142,10930= 4,109607,107902,106668,109121,109101,109056,4621,109031,2574,5092,1674,1064= 52,108901,108849,108713,108783,108766,108386,108455,2560,108397,1538,2007,1= 08000,108389,108336,108456,36796,28985,108003,108421,108399,4871,106884,637= 1,36026,108204,108022,107941,107967,107911,107928,47944,107010,106640,10703= 7,106994,107011,55313,105862,106332,106498,5850,13369,106161,5859,28465,106= 385,106444,102751,106371,105131,2610,102753,4833,4936,4755,4699,105402,1408= 7,4798,4942,36249,55513,75790,75789,4238,6370,5744,5745,5149,4731,42297,348= 41,31190,17339,31155,31242,17701,17642,31203,31218,31376,5856,5141,18154,27= 146,17590,17566,13692,4867,1842,6365,6354,5480,5481,4382,5893,6355,5907,588= 6,5826,5028,4665,5230,5482,5273,4181,5091,4869,4983,4968,4961,4905,4906,403= 6,1483,4284,4790,4348,4648,4655,4647,4656,3075,4596,2144,4274,4592,4506,454= 9,4595,4188,4548,4511,4333,4306,4291,4240,4268,4114,3665,3547,1563,2102,151= 4,3579,3607,3501,2834,2436,3069,1400,2359,3056,3173,2897,2837,2780,2137,144= 7,1280,421,412,2076,1200,1691,446,1444,399,374,444,419,449}'::integer[])) -> Index Scan using do_event_tree_id on=20 do_event ea (cost=3D0.00..5.29 rows=3D1 width=3D16) (actual time=3D0.005..= 0.040=20 rows=3D59 loops=3D233) Index Cond: (tree_id =3D e.tree_id) Total runtime: 24.853 ms (24 rows) If if has >=3D234 values, it is very slow: Unique (cost=3D2379.26..2381.14 rows=3D250 width=3D33) (actual=20 time=3D3851.030..3851.175 rows=3D163 loops=3D1) -> Sort (cost=3D2379.26..2379.89 rows=3D250 width=3D33) (actual=20 time=3D3851.027..3851.073 rows=3D166 loops=3D1) Sort Key: e.id, t.id, (CASE WHEN (ea.id <> e.id) THEN true=20 ELSE false END) Sort Method: quicksort Memory: 37kB -> Nested Loop (cost=3D139.77..2369.30 rows=3D250 width=3D33)=20 (actual time=3D44.373..3850.597 rows=3D166 loops=3D1) Join Filter: ((ea.lft <=3D e.lft) AND (ea.rght >=3D e.rght)) -> Hash Join (cost=3D139.77..1286.83 rows=3D1612 width=3D= 41)=20 (actual time=3D10.784..41.107 rows=3D2312 loops=3D1) Hash Cond: (ea.id =3D et.event_id) -> Seq Scan on do_event ea (cost=3D0.00..840.97=20 rows=3D28997 width=3D16) (actual time=3D0.013..10.275 rows=3D28997 loops=3D= 1) -> Hash (cost=3D119.62..119.62 rows=3D1612 width=3D= 29)=20 (actual time=3D10.607..10.607 rows=3D2312 loops=3D1) Buckets: 1024 Batches: 1 Memory Usage: 137kB -> Hash Join (cost=3D4.73..119.62 rows=3D1612= =20 width=3D29) (actual time=3D0.468..8.024 rows=3D2312 loops=3D1) Hash Cond: (et.tag_id =3D t.id) -> Seq Scan on do_event_tags et=20=20 (cost=3D0.00..79.47 rows=3D5147 width=3D12) (actual time=3D0.007..2.578=20 rows=3D5147 loops=3D1) -> Hash (cost=3D4.08..4.08 rows=3D52=20 width=3D21) (actual time=3D0.434..0.434 rows=3D49 loops=3D1) Buckets: 1024 Batches: 1 Memory=20 Usage: 3kB -> Seq Scan on do_tag t=20 (cost=3D0.00..4.08 rows=3D52 width=3D21) (actual time=3D0.030..0.405 rows= =3D49=20 loops=3D1) Filter: ((user_id =3D 14) OR= =20 shared) -> Index Scan using do_event_tree_id on do_event e=20=20 (cost=3D0.00..0.65 rows=3D1 width=3D16) (actual time=3D0.803..1.644 rows=3D= 1=20 loops=3D2312) Index Cond: (tree_id =3D ea.tree_id) Filter: (id =3D ANY=20 ('{110364,110377,42337,1503,5490,106267,106607,108419,108836,108556,108744,= 108466,108467,106331,3717,105404,35179,3398,5675,5896,5888,5287,4679,4275,4= 042,1599,4041,3311,1588,1605,1607,1606,1604,1594,1850,110494,110041,107955,= 110373,110068,110114,109503,109925,108959,108964,109189,109598,109142,10930= 4,109607,107902,106668,109121,109101,109056,4621,109031,2574,5092,1674,1064= 52,108901,108849,108713,108783,108766,108386,108455,2560,108397,1538,2007,1= 08000,108389,108336,108456,36796,28985,108003,108421,108399,4871,106884,637= 1,36026,108204,108022,107941,107967,107911,107928,47944,107010,106640,10703= 7,106994,107011,55313,105862,106332,106498,5850,13369,106161,5859,28465,106= 385,106444,102751,106371,105131,2610,102753,4833,4936,4755,4699,105402,1408= 7,4798,4942,36249,55513,75790,75789,4238,6370,5744,5745,5149,4731,42297,348= 41,31190,17339,31155,31242,17701,17642,31203,31218,31376,5856,5141,18154,27= 146,17590,17566,13692,4867,1842,6365,6354,5480,5481,4382,5893,6355,5907,588= 6,5826,5028,4665,5230,5482,5273,4181,5091,4869,4983,4968,4961,4905,4906,403= 6,1483,4284,4790,4348,4648,4655,4647,4656,3075,4596,2144,4274,4592,4506,454= 9,4595,4188,4548,4511,4333,4306,4291,4240,4268,4114,3665,3547,1563,2102,151= 4,3579,3607,3501,2834,2436,3069,1400,2359,3056,3173,2897,2837,2780,2137,144= 7,1280,421,412,2076,1200,1691,446,1444,399,374,444,419,449,1021}'::integer[= ])) Total runtime: 3851.458 ms (22 rows) Increasing effective_cache_size or work_mem does not seem to have impact. Thanks for any hint, John --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance