Search Postgresql Archives

[no subject]

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

 



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.





[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