Hi, On Fri, Jul 2, 2021, 5:24 AM Atul Kumar <akumar14...@gmail.com> 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. > > >