Re:

Hi,

On Fri, Jul 2, 2021, 5:24 AM Atul Kumar <akumar14871(at)gmail(dot)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.
>
>
>