Hi,
On Fri, Jul 2, 2021, 5:24 AM Atul Kumar <akumar14871@xxxxxxxxx> wrote:
Hi,
I have one query like below :
SELECT
m.iMemberId "memberId",
m.cFirstName "firstName",
m.cLastName "lastName",
m.cFirstName || ' ' ||
m.cLastName "fullName",
m.cPlayerStateId "stateId",
DECODE(m.cBirthdateVerify, 1,
'Yes', 'No') "birthdateVerify",
TO_CHAR(m.dBirthDate,
'MM/DD/YYYY') "dateOfBirth",
p.cPosition "position",
p.cJerseyNumber "number",
DECODE(daps.status, 2, 'PT',
1, 'FT', NULL) "daps",
op.cCitizenship "citizenship",
op.cNotes "notes",
NVL(op.cCountryOfBirth,
op.cCountryOfBirthOther) "countryOfBirth"
FROM sam_gameroster r
INNER JOIN sam_guestParticipant p ON
p.iMemberId = r.iMemberId AND p.iContainerId = r.iContainerId
INNER JOIN sam_member m ON m.iMemberId
= p.iMemberId
INNER JOIN sam_container c ON
c.iContainerId = r.iContainerId
LEFT JOIN sam_container lc ON
c.iContainerLinkId = lc.iContainerId
LEFT JOIN sam_participant op ON
op.iassignContainerId = NVL(c.iContainerLinkId, c.iContainerId) AND
op.imemberID = m.imemberId
LEFT JOIN (
SELECT pp.iMemberId,
MAX(CASE WHEN
pp.cDpFtStatus = 'PT' THEN 2
WHEN
pp.cDpFtStatus = 'FT' THEN 1
ELSE 0 END) status
FROM sam_participant pp
WHERE pp.igroupid =
getGroupId() GROUP BY pp.iMemberId
) daps ON daps.iMemberId = r.iMemberId
LEFT JOIN sam_playersuspension ps ON
ps.iMemberId = m.iMemberId AND ps.iEventId = r.iEventId
WHERE r.iEventId = '7571049' AND
r.iContainerId = '15257396' AND ((ps.iIsAutoCreated = 1 AND
ps.iIsTombstone = 1) OR ps.iPlayerSuspensionId IS NULL)
ORDER BY LOWER(m.cLastName), LOWER(m.cFirstName)
And the execution of above query is
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=783789.11..783789.11 rows=1 width=377) (actual
time=12410.619..12410.619 rows=0 loops=1)
Sort Key: (lower((m.clastname)::text)), (lower((m.cfirstname)::text))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=525065
-> Merge Right Join (cost=781822.64..783789.10 rows=1 width=377)
(actual time=12410.609..12410.609 rows=0 loops=1)
Merge Cond: (pp.imemberid = r.imemberid)
Buffers: shared hit=525065
-> GroupAggregate (cost=781820.08..783074.57 rows=55308
width=11) (actual time=12410.251..12410.251 rows=1 loops=1)
Group Key: pp.imemberid
Buffers: shared hit=524884
-> Sort (cost=781820.08..781960.36 rows=56113
width=10) (actual time=12410.234..12410.234 rows=3 loops=1)
Sort Key: pp.imemberid
Sort Method: quicksort Memory: 207217kB
Buffers: shared hit=524884
-> Seq Scan on sam_participant pp
(cost=0.00..777393.87 rows=56113 width=10) (actual
time=0.284..10871.913 rows=2335154 loops=1)
Filter: ((igroupid)::integer =
(current_setting('env.groupid'::text))::integer)
Rows Removed by Filter: 8887508
Buffers: shared hit=524884
-> Materialize (cost=2.56..23.14 rows=1 width=184) (actual
time=0.354..0.354 rows=0 loops=1)
Buffers: shared hit=181
-> Nested Loop Left Join (cost=2.56..23.14 rows=1
width=184) (actual time=0.352..0.353 rows=0 loops=1)
Join Filter: (ps.ieventid = r.ieventid)
Filter: (((ps.iisautocreated = '1'::numeric) AND
(ps.iistombstone = '1'::numeric)) OR (ps.iplayersuspensionid IS NULL))
Buffers: shared hit=181
-> Nested Loop Left Join (cost=2.28..22.82
rows=1 width=190) (actual time=0.351..0.351 rows=0 loops=1)
Join Filter: (op.iassigncontainerid =
nvl(c.icontainerlinkid, c.icontainerid))
Buffers: shared hit=181
-> Nested Loop (cost=1.84..21.95 rows=1
width=159) (actual time=0.350..0.350 rows=0 loops=1)
Buffers: shared hit=181
-> Nested Loop (cost=1.41..13.49
rows=1 width=153) (actual time=0.349..0.350 rows=0 loops=1)
Join Filter: (r.imemberid = p.imemberid)
Buffers: shared hit=181
-> Nested Loop
(cost=0.99..13.04 rows=1 width=69) (actual time=0.051..0.274 rows=25
loops=1)
Buffers: shared hit=106
-> Index Only Scan using
gmr_pk on sam_gameroster r (cost=0.56..4.58 rows=1 width=19) (actual
time=0.029..0.051 rows=25 loops=1)
Index Cond:
((ieventid = '7571049'::numeric) AND (icontainerid =
'15257396'::numeric))
Heap Fetches: 0
Buffers: shared hit=5
-> Index Scan using
member_pk on sam_member m (cost=0.43..8.45 rows=1 width=50) (actual
time=0.007..0.007 rows=1 loops=25)
Index Cond:
(imemberid = r.imemberid)
Buffers: shared hit=101
-> Index Scan using gp_pk on
sam_guestparticipant p (cost=0.42..0.44 rows=1 width=97) (actual
time=0.002..0.002 rows=0 loops=25)
Index Cond:
((icontainerid = '15257396'::numeric) AND (imemberid = m.imemberid))
Buffers: shared hit=75
-> Index Scan using cont_pk on
sam_container c (cost=0.43..8.45 rows=1 width=12) (never executed)
Index Cond: (icontainerid =
'15257396'::numeric)
-> Index Scan using newindex5 on
sam_participant op (cost=0.43..0.76 rows=7 width=56) (never executed)
Index Cond: (imemberid = m.imemberid)
-> Index Scan using uniq_psusp_memb_event on
sam_playersuspension ps (cost=0.29..0.31 rows=1 width=26) (never
executed)
Index Cond: ((imemberid = m.imemberid) AND
(ieventid = '7571049'::numeric))
Planning time: 2.818 ms
Execution time: 12416.544 ms
(52 rows)
issue I Found out:
-> Seq Scan on sam_participant pp (cost=0.00..777393.87 rows=56113
width=10) (actual time=0.277..10869.750 rows=2335154 loops=1)
Filter: ((igroupid)::integer =
(current_setting('env.groupid'::text))::integer)
Rows Removed by Filter: 8887508
Buffers: shared hit=524884
I have already an index on the column igroupid of table
sam_participant, but still it is doig seq scan, which is time
consuming or is their something else is fishy.
Can someone please help me by giving one's feedback.
Could you please show the tables schema involved?
Thank you.
Regards.
