Query never completes with an OR condition - Mailing list pgsql-general
| From | Kenneth Marshall |
|---|---|
| Subject | Query never completes with an OR condition |
| Date | |
| Msg-id | 20181203162836.GC16983@aart.rice.edu Whole thread Raw |
| Responses |
Re: Query never completes with an OR condition
(Andrew Gierth <andrew@tao11.riddles.org.uk>)
|
| List | pgsql-general |
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
CachedGroupMembersCachedGroupMembers_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.71rows=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=192loops=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=48loops=1)
Buffers: shared hit=88 read=7
-> Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_1
(cost=0.43..8.49rows=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.958rows=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.914rows=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.016rows=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=18width=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
CachedGroupMembersCachedGroupMembers_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.71rows=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=148loops=1)
Buffers: shared hit=223
-> Nested Loop (cost=60.74..15885.97 rows=18304 width=16) (actual time=0.036..0.184
rows=37loops=1)
Buffers: shared hit=66
-> Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_1
(cost=0.43..8.49rows=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.036rows=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.007rows=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.010rows=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=18width=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
JOINGroups Groups_3 ON ( LOWER(Groups_3.Domain) = 'rt::ticket-role' ) AND ( Groups_3.Instance = main.id ) LEFT JOIN
CachedGroupMembersCachedGroupMembers_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=2323945width=8)
Filter: (disabled = '0'::smallint)
-> Index Only Scan using cachedgroupmembers4 on cachedgroupmembers cachedgroupmembers_2
(cost=0.43..0.90rows=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.71rows=14 width=8)
Index Cond: ((memberid = 579312) AND (disabled = '0'::smallint))
(28 rows)
pgsql-general by date: