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.
Regards.
