Search Postgresql Archives

Re:

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux