Query never completes with an OR condition

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Query never completes with an OR condition

Kenneth Marshall-3
Hi,

I was investigating a performance problem and found a query that
never completes in a reasonable amount of time even though my
expectation is that it should. I am running version 9.6.6. I
do not see anything in the 9.6.* release notes that mention this
problem. The individual queries run as expected, but when the
OR condition is added, it never finishes. Here is the information
about the tables and queries. Any ideas about what could be happening
or how to debug it further would be appreciated.

Regards,
Ken

----------------------------------------------------------------



\d cachedgroupmembers
                                   Table "public.cachedgroupmembers"
      Column       |   Type   |                                Modifiers                                
-------------------+----------+-------------------------------------------------------------------------
 id                | integer  | not null default nextval(('cachedgroupmembers_id_seq'::text)::regclass)
 groupid           | integer  |
 memberid          | integer  |
 via               | integer  |
 immediateparentid | integer  |
 disabled          | smallint | not null default 0
Indexes:
    "cachedgroupmembers_pkey" PRIMARY KEY, btree (id)
    "cachedgroupmembers1" btree (memberid, immediateparentid)
    "cachedgroupmembers4" btree (memberid, groupid, disabled)
    "disgroumem" btree (groupid, memberid, disabled)
    "shredder_cgm2" btree (immediateparentid, memberid)
    "shredder_cgm3" btree (via, id)

\d tickets
                                            Table "public.tickets"
     Column      |            Type             |                          Modifiers                          
-----------------+-----------------------------+--------------------------------------------------------------
 id              | integer                     | not null default nextval(('tickets_id_seq'::text)::regclass)
 effectiveid     | integer                     | not null default 0
 queue           | integer                     | not null default 0
 type            | character varying(16)       |
 owner           | integer                     | not null default 0
 subject         | character varying(200)      | default '[no subject]'::character varying
 initialpriority | integer                     | not null default 0
 finalpriority   | integer                     | not null default 0
 priority        | integer                     | not null default 0
 timeestimated   | integer                     | not null default 0
 timeworked      | integer                     | not null default 0
 status          | character varying(64)       |
 timeleft        | integer                     | not null default 0
 told            | timestamp without time zone |
 starts          | timestamp without time zone |
 started         | timestamp without time zone |
 due             | timestamp without time zone |
 resolved        | timestamp without time zone |
 lastupdatedby   | integer                     | not null default 0
 lastupdated     | timestamp without time zone |
 creator         | integer                     | not null default 0
 created         | timestamp without time zone |
 ismerged        | smallint                    |
 sla             | character varying(64)       |
Indexes:
    "tickets_pkey" PRIMARY KEY, btree (id)
    "tickets1" btree (queue, status)
    "tickets2" btree (owner)
    "tickets3" btree (effectiveid)
    "tickets4" btree (status)
    "tickets_merged_helper" btree ((effectiveid - id))

EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN CachedGroupMembers CachedGroupMembers_1  ON ( CachedGroupMembers_1.Disabled = '0' ) AND ( CachedGroupMembers_1.MemberId = main.Owner ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.MemberId = main.Owner ) LEFT JOIN Groups Groups_3  ON ( LOWER(Groups_3.Domain) = 'rt::ticket-role' ) AND ( Groups_3.Instance = main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_4  ON ( CachedGroupMembers_4.Disabled = '0' ) AND ( CachedGroupMembers_4.MemberId = '579312' ) AND ( CachedGroupMembers_4.GroupId = Groups_3.id )  WHERE ( ( main.Queue IN ('3', '3', '7', '7', '4', '4', '6', '6', '18', '18', '1', '1', '8', '8', '17', '17', '11', '11', '15', '15', '23', '23', '20', '20', '22', '22', '21', '21', '12', '12', '14', '14', '19', '19', '10', '10', '5', '5', '9', '9', '28', '28', '38', '38', '30', '30', '34', '34', '37', '37', '31', '31', '32', '32', '36', '36', '33', '33', '35', '35', '41', '41', '40', '40', '42', '42', '43', '43', '39', '39', '51', '51', '52', '52', '53', '53', '61', '63', '63', '64', '66', '68', '69', '69', '71', '71', '72', '72', '73', '73', '70', '70', '75', '75', '81', '81', '82', '82', '83', '83', '84', '84', '86', '86', '87', '89', '89', '90', '90', '92', '92', '93', '93', '94', '94', '91', '24', '24', '95', '95', '97', '97', '98', '98', '103', '103', '105', '106', '106', '107', '107', '112', '112', '109', '109', '110', '110', '111', '111', '113', '113', '114', '114', '117', '117', '118', '118', '120', '120', '119', '119', '116', '116', '115', '115', '121', '121', '122', '122', '124', '124', '123', '123', '125', '125', '126', '126', '127', '127', '137', '138', '138', '140', '140', '133', '134', '133', '153', '153', '154', '154', '155', '155', '156', '156', '132', '157', '157', '158', '158', '160', '160', '162', '162', '165', '165', '167', '168', '169', '169', '170', '170', '173', '179', '179', '181', '181', '184', '184', '185', '185', '187', '187', '164', '164', '190', '190', '191', '228', '80', '80', '230', '230', '231', '231', '183', '183', '232', '232', '27', '233', '13', '13', '241', '242', '243', '243', '245', '244', '251', '16', '29', '253', '253', '256', '257', '257', '242', '258', '261') OR  ( CachedGroupMembers_4.MemberId IS NOT NULL AND LOWER(Groups_3.Name) = 'requestor' )  OR  ( main.Owner = '579312' )  OR  ( CachedGroupMembers_4.MemberId IS NOT NULL AND LOWER(Groups_3.Name) = 'admincc' )  ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND CachedGroupMembers_1.GroupId = '3424108';
                                                                                                                                                                                                                   
                                                                                                                                                                                                                   
                                                                                                                                                 QUERY PLAN                                                        
                                                                                                                                                                                                                   
                                                                                                                                                                                                                   
                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
 Aggregate  (cost=890494.02..890494.03 rows=1 width=8) (actual time=81.250..81.250 rows=1 loops=1)
   Buffers: shared hit=9273 read=20
   ->  Nested Loop  (cost=32766.63..91115.22 rows=319751521 width=4) (actual time=31.596..73.081 rows=45688 loops=1)
         Join Filter: (cachedgroupmembers_1.memberid = cachedgroupmembers_2.memberid)
         Buffers: shared hit=9273 read=20
         ->  Hash Right Join  (cost=32766.20..32774.87 rows=51916 width=12) (actual time=24.132..24.740 rows=192 loops=1)
               Hash Cond: (cachedgroupmembers_4.groupid = groups_3.id)
               Filter: ((main.queue = ANY ('{3,3,7,7,4,4,6,6,18,18,1,1,8,8,17,17,11,11,15,15,23,23,20,20,22,22,21,21,12,12,14,14,19,19,10,10,5,5,9,9,28,28,38,38,30,30,34,34,37,37,31,31,32,32,36,36,33,33,35,35,41
,41,40,40,42,42,43,43,39,39,51,51,52,52,53,53,61,63,63,64,66,68,69,69,71,71,72,72,73,73,70,70,75,75,81,81,82,82,83,83,84,84,86,86,87,89,89,90,90,92,92,93,93,94,94,91,24,24,95,95,97,97,98,98,103,103,105,106,106,1
07,107,112,112,109,109,110,110,111,111,113,113,114,114,117,117,118,118,120,120,119,119,116,116,115,115,121,121,122,122,124,124,123,123,125,125,126,126,127,127,137,138,138,140,140,133,134,133,153,153,154,154,155,
155,156,156,132,157,157,158,158,160,160,162,162,165,165,167,168,169,169,170,170,173,179,179,181,181,184,184,185,185,187,187,164,164,190,190,191,228,80,80,230,230,231,231,183,183,232,232,27,233,13,13,241,242,243,
243,245,244,251,16,29,253,253,256,257,257,242,258,261}'::integer[])) OR ((cachedgroupmembers_4.memberid IS NOT NULL) AND (lower((groups_3.name)::text) = 'requestor'::text)) OR (main.owner = 579312) OR ((cachedgr
oupmembers_4.memberid IS NOT NULL) AND (lower((groups_3.name)::text) = 'admincc'::text)))
               Buffers: shared hit=331 read=9
               ->  Index Only Scan using cachedgroupmembers4 on cachedgroupmembers cachedgroupmembers_4  (cost=0.43..8.71 rows=14 width=8) (actual time=11.197..11.229 rows=123 loops=1)
                     Index Cond: ((memberid = 579312) AND (disabled = '0'::smallint))
                     Heap Fetches: 24
                     Buffers: shared hit=40 read=2
               ->  Hash  (cost=32000.44..32000.44 rows=61226 width=26) (actual time=12.729..12.729 rows=192 loops=1)
                     Buckets: 65536  Batches: 1  Memory Usage: 524kB
                     Buffers: shared hit=291 read=7
                     ->  Nested Loop Left Join  (cost=61.17..32000.44 rows=61226 width=26) (actual time=0.163..12.646 rows=192 loops=1)
                           Buffers: shared hit=291 read=7
                           ->  Nested Loop  (cost=60.74..15885.97 rows=18304 width=16) (actual time=0.128..11.807 rows=48 loops=1)
                                 Buffers: shared hit=88 read=7
                                 ->  Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_1  (cost=0.43..8.49 rows=3 width=4) (actual time=0.018..0.029 rows=6 loops=1)
                                       Index Cond: ((groupid = 3424108) AND (disabled = '0'::smallint))
                                       Heap Fetches: 3
                                       Buffers: shared hit=7
                                 ->  Bitmap Heap Scan on tickets main  (cost=60.31..5278.15 rows=1434 width=12) (actual time=1.925..1.958 rows=8 loops=6)
                                       Recheck Cond: (owner = cachedgroupmembers_1.memberid)
                                       Filter: ((ismerged IS NULL) AND ((status)::text <> 'deleted'::text) AND ((type)::text = 'ticket'::text))
                                       Rows Removed by Filter: 1
                                       Heap Blocks: exact=70
                                       Buffers: shared hit=81 read=7
                                       ->  Bitmap Index Scan on tickets2  (cost=0.00..59.95 rows=1537 width=0) (actual time=1.914..1.914 rows=14 loops=6)
                                             Index Cond: (owner = cachedgroupmembers_1.memberid)
                                             Buffers: shared hit=11 read=7
                           ->  Index Scan using groups3 on groups groups_3  (cost=0.43..0.84 rows=4 width=14) (actual time=0.012..0.016 rows=4 loops=48)
                                 Index Cond: (instance = main.id)
                                 Filter: (lower((domain)::text) = 'rt::ticket-role'::text)
                                 Rows Removed by Filter: 0
                                 Buffers: shared hit=203
         ->  Index Only Scan using cachedgroupmembers4 on cachedgroupmembers cachedgroupmembers_2  (cost=0.43..0.90 rows=18 width=4) (actual time=0.111..0.209 rows=238 loops=192)
               Index Cond: ((memberid = main.owner) AND (disabled = '0'::smallint))
               Heap Fetches: 5028
               Buffers: shared hit=8942 read=11
 Planning time: 2.858 ms
 Execution time: 81.335 ms
(44 rows)

EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN CachedGroupMembers CachedGroupMembers_1  ON ( CachedGroupMembers_1.Disabled = '0' ) AND ( CachedGroupMembers_1.MemberId = main.Owner ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.MemberId = main.Owner ) LEFT JOIN Groups Groups_3  ON ( LOWER(Groups_3.Domain) = 'rt::ticket-role' ) AND ( Groups_3.Instance = main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_4  ON ( CachedGroupMembers_4.Disabled = '0' ) AND ( CachedGroupMembers_4.MemberId = '579312' ) AND ( CachedGroupMembers_4.GroupId = Groups_3.id )  WHERE ( ( main.Queue IN ('3', '3', '7', '7', '4', '4', '6', '6', '18', '18', '1', '1', '8', '8', '17', '17', '11', '11', '15', '15', '23', '23', '20', '20', '22', '22', '21', '21', '12', '12', '14', '14', '19', '19', '10', '10', '5', '5', '9', '9', '28', '28', '38', '38', '30', '30', '34', '34', '37', '37', '31', '31', '32', '32', '36', '36', '33', '33', '35', '35', '41', '41', '40', '40', '42', '42', '43', '43', '39', '39', '51', '51', '52', '52', '53', '53', '61', '63', '63', '64', '66', '68', '69', '69', '71', '71', '72', '72', '73', '73', '70', '70', '75', '75', '81', '81', '82', '82', '83', '83', '84', '84', '86', '86', '87', '89', '89', '90', '90', '92', '92', '93', '93', '94', '94', '91', '24', '24', '95', '95', '97', '97', '98', '98', '103', '103', '105', '106', '106', '107', '107', '112', '112', '109', '109', '110', '110', '111', '111', '113', '113', '114', '114', '117', '117', '118', '118', '120', '120', '119', '119', '116', '116', '115', '115', '121', '121', '122', '122', '124', '124', '123', '123', '125', '125', '126', '126', '127', '127', '137', '138', '138', '140', '140', '133', '134', '133', '153', '153', '154', '154', '155', '155', '156', '156', '132', '157', '157', '158', '158', '160', '160', '162', '162', '165', '165', '167', '168', '169', '169', '170', '170', '173', '179', '179', '181', '181', '184', '184', '185', '185', '187', '187', '164', '164', '190', '190', '191', '228', '80', '80', '230', '230', '231', '231', '183', '183', '232', '232', '27', '233', '13', '13', '241', '242', '243', '243', '245', '244', '251', '16', '29', '253', '253', '256', '257', '257', '242', '258', '261') OR  ( CachedGroupMembers_4.MemberId IS NOT NULL AND LOWER(Groups_3.Name) = 'requestor' )  OR  ( main.Owner = '579312' )  OR  ( CachedGroupMembers_4.MemberId IS NOT NULL AND LOWER(Groups_3.Name) = 'admincc' )  ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND CachedGroupMembers_1.GroupId = '3424109';
                                                                                                                                                                                                                   
                                                                                                                                                                                                                   
                                                                                                                                                 QUERY PLAN                                                        
                                                                                                                                                                                                                   
                                                                                                                                                                                                                   
                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
 Aggregate  (cost=890494.02..890494.03 rows=1 width=8) (actual time=21.748..21.748 rows=1 loops=1)
   Buffers: shared hit=7778
   ->  Nested Loop  (cost=32766.63..91115.22 rows=319751521 width=4) (actual time=0.755..16.059 rows=37104 loops=1)
         Join Filter: (cachedgroupmembers_1.memberid = cachedgroupmembers_2.memberid)
         Buffers: shared hit=7778
         ->  Hash Right Join  (cost=32766.20..32774.87 rows=51916 width=12) (actual time=0.747..1.236 rows=148 loops=1)
               Hash Cond: (cachedgroupmembers_4.groupid = groups_3.id)
               Filter: ((main.queue = ANY ('{3,3,7,7,4,4,6,6,18,18,1,1,8,8,17,17,11,11,15,15,23,23,20,20,22,22,21,21,12,12,14,14,19,19,10,10,5,5,9,9,28,28,38,38,30,30,34,34,37,37,31,31,32,32,36,36,33,33,35,35,41
,41,40,40,42,42,43,43,39,39,51,51,52,52,53,53,61,63,63,64,66,68,69,69,71,71,72,72,73,73,70,70,75,75,81,81,82,82,83,83,84,84,86,86,87,89,89,90,90,92,92,93,93,94,94,91,24,24,95,95,97,97,98,98,103,103,105,106,106,1
07,107,112,112,109,109,110,110,111,111,113,113,114,114,117,117,118,118,120,120,119,119,116,116,115,115,121,121,122,122,124,124,123,123,125,125,126,126,127,127,137,138,138,140,140,133,134,133,153,153,154,154,155,
155,156,156,132,157,157,158,158,160,160,162,162,165,165,167,168,169,169,170,170,173,179,179,181,181,184,184,185,185,187,187,164,164,190,190,191,228,80,80,230,230,231,231,183,183,232,232,27,233,13,13,241,242,243,
243,245,244,251,16,29,253,253,256,257,257,242,258,261}'::integer[])) OR ((cachedgroupmembers_4.memberid IS NOT NULL) AND (lower((groups_3.name)::text) = 'requestor'::text)) OR (main.owner = 579312) OR ((cachedgr
oupmembers_4.memberid IS NOT NULL) AND (lower((groups_3.name)::text) = 'admincc'::text)))
               Buffers: shared hit=265
               ->  Index Only Scan using cachedgroupmembers4 on cachedgroupmembers cachedgroupmembers_4  (cost=0.43..8.71 rows=14 width=8) (actual time=0.010..0.047 rows=123 loops=1)
                     Index Cond: ((memberid = 579312) AND (disabled = '0'::smallint))
                     Heap Fetches: 24
                     Buffers: shared hit=42
               ->  Hash  (cost=32000.44..32000.44 rows=61226 width=26) (actual time=0.658..0.658 rows=148 loops=1)
                     Buckets: 65536  Batches: 1  Memory Usage: 521kB
                     Buffers: shared hit=223
                     ->  Nested Loop Left Join  (cost=61.17..32000.44 rows=61226 width=26) (actual time=0.051..0.599 rows=148 loops=1)
                           Buffers: shared hit=223
                           ->  Nested Loop  (cost=60.74..15885.97 rows=18304 width=16) (actual time=0.036..0.184 rows=37 loops=1)
                                 Buffers: shared hit=66
                                 ->  Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_1  (cost=0.43..8.49 rows=3 width=4) (actual time=0.009..0.020 rows=4 loops=1)
                                       Index Cond: ((groupid = 3424109) AND (disabled = '0'::smallint))
                                       Heap Fetches: 1
                                       Buffers: shared hit=5
                                 ->  Bitmap Heap Scan on tickets main  (cost=60.31..5278.15 rows=1434 width=12) (actual time=0.012..0.036 rows=9 loops=4)
                                       Recheck Cond: (owner = cachedgroupmembers_1.memberid)
                                       Filter: ((ismerged IS NULL) AND ((status)::text <> 'deleted'::text) AND ((type)::text = 'ticket'::text))
                                       Rows Removed by Filter: 2
                                       Heap Blocks: exact=49
                                       Buffers: shared hit=61
                                       ->  Bitmap Index Scan on tickets2  (cost=0.00..59.95 rows=1537 width=0) (actual time=0.007..0.007 rows=14 loops=4)
                                             Index Cond: (owner = cachedgroupmembers_1.memberid)
                                             Buffers: shared hit=12
                           ->  Index Scan using groups3 on groups groups_3  (cost=0.43..0.84 rows=4 width=14) (actual time=0.007..0.010 rows=4 loops=37)
                                 Index Cond: (instance = main.id)
                                 Filter: (lower((domain)::text) = 'rt::ticket-role'::text)
                                 Rows Removed by Filter: 0
                                 Buffers: shared hit=157
         ->  Index Only Scan using cachedgroupmembers4 on cachedgroupmembers cachedgroupmembers_2  (cost=0.43..0.90 rows=18 width=4) (actual time=0.004..0.055 rows=251 loops=148)
               Index Cond: ((memberid = main.owner) AND (disabled = '0'::smallint))
               Heap Fetches: 4536
               Buffers: shared hit=7513
 Planning time: 2.869 ms
 Execution time: 21.823 ms
(44 rows)

EXPLAIN SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN CachedGroupMembers CachedGroupMembers_1  ON ( CachedGroupMembers_1.Disabled = '0' ) AND ( CachedGroupMembers_1.MemberId = main.Owner ) JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.MemberId = main.Owner ) LEFT JOIN Groups Groups_3  ON ( LOWER(Groups_3.Domain) = 'rt::ticket-role' ) AND ( Groups_3.Instance = main.id ) LEFT JOIN CachedGroupMembers CachedGroupMembers_4  ON ( CachedGroupMembers_4.Disabled = '0' ) AND ( CachedGroupMembers_4.MemberId = '579312' ) AND ( CachedGroupMembers_4.GroupId = Groups_3.id )  WHERE ( ( main.Queue IN ('3', '3', '7', '7', '4', '4', '6', '6', '18', '18', '1', '1', '8', '8', '17', '17', '11', '11', '15', '15', '23', '23', '20', '20', '22', '22', '21', '21', '12', '12', '14', '14', '19', '19', '10', '10', '5', '5', '9', '9', '28', '28', '38', '38', '30', '30', '34', '34', '37', '37', '31', '31', '32', '32', '36', '36', '33', '33', '35', '35', '41', '41', '40', '40', '42', '42', '43', '43', '39', '39', '51', '51', '52', '52', '53', '53', '61', '63', '63', '64', '66', '68', '69', '69', '71', '71', '72', '72', '73', '73', '70', '70', '75', '75', '81', '81', '82', '82', '83', '83', '84', '84', '86', '86', '87', '89', '89', '90', '90', '92', '92', '93', '93', '94', '94', '91', '24', '24', '95', '95', '97', '97', '98', '98', '103', '103', '105', '106', '106', '107', '107', '112', '112', '109', '109', '110', '110', '111', '111', '113', '113', '114', '114', '117', '117', '118', '118', '120', '120', '119', '119', '116', '116', '115', '115', '121', '121', '122', '122', '124', '124', '123', '123', '125', '125', '126', '126', '127', '127', '137', '138', '138', '140', '140', '133', '134', '133', '153', '153', '154', '154', '155', '155', '156', '156', '132', '157', '157', '158', '158', '160', '160', '162', '162', '165', '165', '167', '168', '169', '169', '170', '170', '173', '179', '179', '181', '181', '184', '184', '185', '185', '187', '187', '164', '164', '190', '190', '191', '228', '80', '80', '230', '230', '231', '231', '183', '183', '232', '232', '27', '233', '13', '13', '241', '242', '243', '243', '245', '244', '251', '16', '29', '253', '253', '256', '257', '257', '242', '258', '261') OR  ( CachedGroupMembers_4.MemberId IS NOT NULL AND LOWER(Groups_3.Name) = 'requestor' )  OR  ( main.Owner = '579312' )  OR  ( CachedGroupMembers_4.MemberId IS NOT NULL AND LOWER(Groups_3.Name) = 'admincc' )  ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND (CachedGroupMembers_1.GroupId = '3424108' OR CachedGroupMembers_2.GroupId = '3424109');
                                                                                                                                                                                                                   
                                                                                                                                                                                                                   
                                                                                                                                                    QUERY PLAN                                                    
                                                                                                                                                                                                                   
                                                                                                                                                                                                                   
                                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
 Aggregate  (cost=15585087.22..15585087.23 rows=1 width=8)
   ->  Merge Join  (cost=2743321.81..13750588.06 rows=733799667 width=4)
         Merge Cond: (cachedgroupmembers_1.memberid = main.owner)
         ->  Sort  (cost=2194090.30..2194196.31 rows=42404 width=8)
               Sort Key: cachedgroupmembers_1.memberid
               ->  Gather  (cost=1000.43..2190831.15 rows=42404 width=8)
                     Workers Planned: 2
                     ->  Nested Loop  (cost=0.43..2185590.75 rows=17668 width=8)
                           ->  Parallel Seq Scan on cachedgroupmembers cachedgroupmembers_1  (cost=0.00..73131.51 rows=2323945 width=8)
                                 Filter: (disabled = '0'::smallint)
                           ->  Index Only Scan using cachedgroupmembers4 on cachedgroupmembers cachedgroupmembers_2  (cost=0.43..0.90 rows=1 width=8)
                                 Index Cond: ((memberid = cachedgroupmembers_1.memberid) AND (disabled = '0'::smallint))
                                 Filter: ((cachedgroupmembers_1.groupid = 3424108) OR (groupid = 3424109))
         ->  Sort  (cost=549231.51..555372.36 rows=2456341 width=8)
               Sort Key: main.owner
               ->  Hash Left Join  (cost=81156.63..288514.50 rows=2456341 width=8)
                     Hash Cond: (groups_3.id = cachedgroupmembers_4.groupid)
                     Filter: ((main.queue = ANY ('{3,3,7,7,4,4,6,6,18,18,1,1,8,8,17,17,11,11,15,15,23,23,20,20,22,22,21,21,12,12,14,14,19,19,10,10,5,5,9,9,28,28,38,38,30,30,34,34,37,37,31,31,32,32,36,36,33,33,35
,35,41,41,40,40,42,42,43,43,39,39,51,51,52,52,53,53,61,63,63,64,66,68,69,69,71,71,72,72,73,73,70,70,75,75,81,81,82,82,83,83,84,84,86,86,87,89,89,90,90,92,92,93,93,94,94,91,24,24,95,95,97,97,98,98,103,103,105,106
,106,107,107,112,112,109,109,110,110,111,111,113,113,114,114,117,117,118,118,120,120,119,119,116,116,115,115,121,121,122,122,124,124,123,123,125,125,126,126,127,127,137,138,138,140,140,133,134,133,153,153,154,15
4,155,155,156,156,132,157,157,158,158,160,160,162,162,165,165,167,168,169,169,170,170,173,179,179,181,181,184,184,185,185,187,187,164,164,190,190,191,228,80,80,230,230,231,231,183,183,232,232,27,233,13,13,241,24
2,243,243,245,244,251,16,29,253,253,256,257,257,242,258,261}'::integer[])) OR ((cachedgroupmembers_4.memberid IS NOT NULL) AND (lower((groups_3.name)::text) = 'requestor'::text)) OR (main.owner = 579312) OR ((ca
chedgroupmembers_4.memberid IS NOT NULL) AND (lower((groups_3.name)::text) = 'admincc'::text)))
                     ->  Hash Right Join  (cost=81147.75..230564.78 rows=2896824 width=22)
                           Hash Cond: (groups_3.instance = main.id)
                           ->  Seq Scan on groups groups_3  (cost=0.00..108806.51 rows=3104608 width=14)
                                 Filter: (lower((domain)::text) = 'rt::ticket-role'::text)
                           ->  Hash  (cost=70322.31..70322.31 rows=866035 width=12)
                                 ->  Seq Scan on tickets main  (cost=0.00..70322.31 rows=866035 width=12)
                                       Filter: ((ismerged IS NULL) AND ((status)::text <> 'deleted'::text) AND ((type)::text = 'ticket'::text))
                     ->  Hash  (cost=8.71..8.71 rows=14 width=8)
                           ->  Index Only Scan using cachedgroupmembers4 on cachedgroupmembers cachedgroupmembers_4  (cost=0.43..8.71 rows=14 width=8)
                                 Index Cond: ((memberid = 579312) AND (disabled = '0'::smallint))
(28 rows)


Reply | Threaded
Open this post in threaded view
|

Re: Query never completes with an OR condition

Andrew Gierth
>>>>> "Kenneth" == Kenneth Marshall <[hidden email]> writes:

 Kenneth> The individual queries run as expected, but when the OR
 Kenneth> condition is added, it never finishes.

http://blog.rhodiumtoad.org.uk/2017/01/22/performance-issues-with-ored-conditions/

--
Andrew (irc:RhodiumToad)

Reply | Threaded
Open this post in threaded view
|

Re: Query never completes with an OR condition

Stephen Frost
Greetings,

* Andrew Gierth ([hidden email]) wrote:
> >>>>> "Kenneth" == Kenneth Marshall <[hidden email]> writes:
>
>  Kenneth> The individual queries run as expected, but when the OR
>  Kenneth> condition is added, it never finishes.
>
> http://blog.rhodiumtoad.org.uk/2017/01/22/performance-issues-with-ored-conditions/

There's been work to improve on this and which I know that I, at least,
would love to see make it into PostgreSQL:

https://commitfest.postgresql.org/19/1001/

Would be great if someone could look at that and try to help determine
if it's possible to ensure that the results are entirely correct.

Thanks!

Stephen

signature.asc (836 bytes) Download Attachment