Search Postgresql Archives

optimization issue

[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)





                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=718009.89..718009.89 rows=1 width=377) (actual
time=6730.489..6730.489 rows=0 loops=1)
   Sort Key: (lower((m.clastname)::text)), (lower((m.cfirstname)::text))
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=402621
   ->  Nested Loop  (cost=686998.22..718009.88 rows=1 width=377)
(actual time=6730.452..6730.452 rows=0 loops=1)
         Join Filter: (r.imemberid = p.imemberid)
         Buffers: shared hit=402618
         ->  Nested Loop Left Join  (cost=686997.80..718009.40 rows=1
width=110) (actual time=6038.397..6730.291 rows=25 loops=1)
               Join Filter: (op.iassigncontainerid =
nvl(c.icontainerlinkid, c.icontainerid))
               Rows Removed by Join Filter: 94
               Buffers: shared hit=402543
               ->  Nested Loop  (cost=686997.37..718008.53 rows=1
width=79) (actual time=6038.363..6729.604 rows=25 loops=1)
                     Buffers: shared hit=402349
                     ->  Hash Right Join  (cost=686996.94..718000.08
rows=1 width=67) (actual time=6038.327..6729.331 rows=25 loops=1)
                           Hash Cond: (pp.imemberid = r.imemberid)
                           Buffers: shared hit=402249
                           ->  HashAggregate
(cost=686983.56..700037.48 rows=1305392 width=11) (actual
time=6026.588..6466.106 rows=996083 loops=1)
                                 Group Key: pp.imemberid
                                 Buffers: shared hit=402093




                                 ->  Bitmap Heap Scan on
sam_participant pp  (cost=87058.78..663894.09 rows=2308947 width=10)
(actual time=508.729..4207.342 rows=2335152 loops=1)
                                       Recheck Cond: (igroupid =
((current_setting('env.groupid'::text))::integer)::numeric)
                                       Heap Blocks: exact=387125
                                       Buffers: shared hit=402093


    Recheck Cond: (igroupid =
((current_setting('env.groupid'::text))::integer)::numeric)
                                       Heap Blocks: exact=113609
                                       Buffers: shared hit=119992




                                       ->  Bitmap Index Scan on
participant_group_inx  (cost=0.00..86481.55 rows=2308947 width=0)
(actual time=402.725..402.725 rows=2335152 loops=1)
                                             Index Cond: (igroupid =
((current_setting('env.groupid'::text))::integer)::numeric)
                                             Buffers: shared hit=14968



                   Index Cond: (igroupid =
((current_setting('env.groupid'::text))::integer)::numeric)
                                             Buffers: shared hit=6383

                           ->  Hash  (cost=13.36..13.36 rows=1
width=63) (actual time=0.873..0.873 rows=25 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                 Buffers: shared hit=156
                                 ->  Nested Loop Left Join
(cost=1.28..13.36 rows=1 width=63) (actual time=0.133..0.856 rows=25
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=156
                                       ->  Nested Loop
(cost=0.99..13.04 rows=1 width=69) (actual time=0.104..0.714 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.059..0.066 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.024..0.024 rows=1 loops=25)
                                                   Index Cond:
(imemberid = r.imemberid)
                                                   Buffers: shared hit=101
                                       ->  Index Scan using
uniq_psusp_memb_event on sam_playersuspension ps  (cost=0.29..0.31
rows=1 width=26) (actual time=0.004..0.004 rows=0 loops=25)
                                             Index Cond: ((imemberid =
m.imemberid) AND (ieventid = '7571049'::numeric))
                                             Buffers: shared hit=50
                     ->  Index Scan using cont_pk on sam_container c
(cost=0.43..8.45 rows=1 width=12) (actual time=0.008..0.008 rows=1
loops=25)
                           Index Cond: (icontainerid = '15257396'::numeric)
                           Buffers: shared hit=100
               ->  Index Scan using newindex5 on sam_participant op
(cost=0.43..0.76 rows=7 width=56) (actual time=0.014..0.024 rows=5
loops=25)
                     Index Cond: (imemberid = m.imemberid)
                     Buffers: shared hit=194
         ->  Index Scan using gp_pk on sam_guestparticipant p
(cost=0.42..0.44 rows=1 width=97) (actual time=0.005..0.005 rows=0
loops=25)
               Index Cond: ((icontainerid = '15257396'::numeric) AND
(imemberid = m.imemberid))
               Buffers: shared hit=75
 Planning time: 7.206 ms
 Execution time: 6741.891 ms
(56 rows)





The query I shared has been calling one function also (function name:
getGroupId())

Below is given function definition too.

CREATE OR REPLACE FUNCTION onesam.getgroupid()
 RETURNS integer
 LANGUAGE sql
AS $function$
        SELECT CAST(current_setting('env.groupid') AS integer);
$function$


-----------------------

Table structure:


greenliv=# \d sam_participant
                               Table "onesam.sam_participant"
          Column          |              Type              |
 Modifiers
--------------------------+--------------------------------+---------------------------------
 iparticipantid           | numeric(22,0)                  | not null
 iassigncontainerid       | numeric(22,0)                  |
 ifamilyid                | numeric(22,0)                  |
 imemberid                | numeric(22,0)                  |
 cwhichresides            | character varying(32)          |
 cfirstname               | character varying(32)          |
 cmiddlename              | character varying(64)          |
 clastname                | character varying(32)          |
 caddress1                | character varying(256)         |
 caddress2                | character varying(256)         |
 ccity                    | character varying(64)          |
 cstate                   | character varying(2)           |
 czip                     | character varying(10)          |
 chomephone               | character varying(30)          |
 cworkphone               | character varying(30)          |
 ccellphone               | character varying(30)          |
 cemail                   | character varying(256)         |
 cgender                  | character varying(1)           |
 dbirthdate               | timestamp without time zone    |
 cshirtsize               | character varying(20)          |
 cdoctorname              | character varying(128)         |
 cdoctorphone             | character varying(30)          |
 cinsname                 | character varying(128)         |
 cinsphone                | character varying(30)          |
 cinsgroup                | character varying(128)         |
 cinsid                   | character varying(128)         |
 tallergies               | character varying(4000)        |
 tspecialneeds            | character varying(4000)        |
 cemergfirstname          | character varying(32)          |
 cemerglastname           | character varying(32)          |
 cemergdayphone           | character varying(30)          |
 cemergevephone           | character varying(30)          |
 cschool                  | character varying(64)          |
 cgrade                   | character varying(32)          |
 cpantsize                | character varying(20)          |
 cyearsexperience         | character varying(13)          |
 tteammatechoice          | character varying(4000)        |
 cuniform                 | character varying(20)          |
 totherfield1             | character varying(4000)        |
 totherfield2             | character varying(4000)        |
 totherfield3             | character varying(4000)        |
 totherfield4             | character varying(4000)        |
 totherfield5             | character varying(4000)        |
 cgradyear                | character varying(32)          |
 cgpa                     | character varying(32)          |
 csat                     | character varying(32)          |
 tbio                     | character varying(4000)        |
 cposition                | character varying(32)          |
 cawards                  | character varying(1000)        |
 iacceptassignment        | numeric(1,0)                   | default 0
 itryout                  | numeric(1,0)                   | default 0
 itryoutmailsent          | numeric(1,0)                   | default 0
 istateid                 | numeric(22,0)                  |
 cnickname                | character varying(32)          |
 cplayerstateid           | character varying(64)          |
 cadminusername           | character varying(320)         |
 dassigntimestamp         | timestamp(6) without time zone |
 iistransfered            | numeric(1,0)                   |
 dcreatedtimestamp        | timestamp(6) without time zone |
 dmodifiedtimestamp       | timestamp(6) without time zone |
 icreatedadminid          | numeric(22,0)                  |
 imodifiedadminid         | numeric(22,0)                  |
 cjerseynumber            | character varying(32)          |
 totherfield6             | character varying(4000)        |
 totherfield7             | character varying(4000)        |
 totherfield8             | character varying(4000)        |
 totherfield9             | character varying(4000)        |
 totherfield10            | character varying(4000)        |
 totherfield11            | character varying(4000)        |
 totherfield12            | character varying(4000)        |
 totherfield13            | character varying(4000)        |
 totherfield14            | character varying(4000)        |
 totherfield15            | character varying(4000)        |
 totherfield16            | character varying(4000)        |
 totherfield17            | character varying(4000)        |
 totherfield18            | character varying(4000)        |
 totherfield19            | character varying(4000)        |
 totherfield20            | character varying(4000)        |
 ireadconcussion          | numeric(1,0)                   | not null default 0
 iregeventid              | numeric(22,0)                  | not null default 0
 iseasonid                | numeric(22,0)                  | not null
default 1000
 ineedsprint              | numeric(1,0)                   | not null default 0
 dlastprint               | timestamp(6) without time zone |
 igroupid                 | numeric(22,0)                  | not null
default getgroupid()
 iuserid                  | numeric(22,0)                  | not null
default getuserid()
 csocksize                | character varying(20)          |
 cjerseynumberpref1       | character varying(32)          |
 cjerseynumberpref2       | character varying(32)          |
 totherfield21            | character varying(4000)        |
 totherfield22            | character varying(4000)        |
 totherfield23            | character varying(4000)        |
 totherfield24            | character varying(4000)        |
 totherfield25            | character varying(4000)        |
 totherfield26            | character varying(4000)        |
 totherfield27            | character varying(4000)        |
 totherfield28            | character varying(4000)        |
 totherfield29            | character varying(4000)        |
 totherfield30            | character varying(4000)        |
 totherfield31            | character varying(4000)        |
 totherfield32            | character varying(4000)        |
 totherfield33            | character varying(4000)        |
 totherfield34            | character varying(4000)        |
 totherfield35            | character varying(4000)        |
 totherfield36            | character varying(4000)        |
 totherfield37            | character varying(4000)        |
 totherfield38            | character varying(4000)        |
 totherfield39            | character varying(4000)        |
 totherfield40            | character varying(4000)        |
 iuniformstatus           | numeric(1,0)                   | not null default 0
 iautoreturn              | numeric(1,0)                   | not null default 1
 icellcarrierid           | numeric(22,0)                  |
 cofficialapplication     | character varying(4000)        |
 iheight                  | numeric(6,0)                   |
 iweight                  | numeric(7,0)                   |
 iisapproved              | numeric(1,0)                   | not null default 0
 citc                     | character varying(256)         |
 ccitizenship             | character varying(256)         |
 ccountryofbirth          | character varying(256)         |
 ccountryofbirthother     | character varying(256)         |
 cnationality             | character varying(256)         |
 cnationalityother        | character varying(256)         |
 iplayedincollege         | numeric(1,0)                   |
 ilivedandplayedoutsideus | numeric(1,0)                   |
 cnotes                   | character varying(1048)        |
 cexternalmemberid        | character varying(128)         |
 cjacketsize              | character varying(20)          |
 cdpftstatus              | character varying(64)          | default
'FT'::character varying
 dapproveddate            | timestamp without time zone    |
 imembertypeid            | integer                        |
 bussfadd                 | boolean                        |
 bisreleased              | boolean                        | default false
 ccounty                  | character varying(100)         |
 cinstagramurl            | character varying(70)          |
 ctwitterurl              | character varying(70)          |
 cleague                  | character varying(100)         |
 clevelofplay             | character varying(50)          |
 cothersport              | character varying(100)         |
 cschooldistrict          | character varying(240)         |
 cschoolstate             | character varying(50)          |
 cusafbid                 | character varying(45)          |
 cussfid                  | text                           |
 cfifaid                  | text                           |
 cuslid                   | character varying(45)          |
 duslexpirationdate       | timestamp without time zone    |
 cuslstatus               | character varying(64)          |
Indexes:
    "part_pk" PRIMARY KEY, btree (iparticipantid)
    "newindex118" btree (istateid)
    "newindex4" btree (ifamilyid)
    "newindex5" btree (imemberid)
    "newindex6" btree (iassigncontainerid)
    "part_mt" btree (imembertypeid)
    "part_needsprint_inx" btree (ineedsprint)
    "part_re" btree (iregeventid)
    "part_se" btree (iseasonid)
    "parti_fl" btree (lower(cfirstname::text) text_pattern_ops,
lower(clastname::text) text_pattern_ops)
    "participant_group_inx" btree (igroupid)
    "participant_uidx" btree (iuserid)
Check constraints:
    "part_papprove_chk" CHECK (iisapproved = ANY (ARRAY[0::numeric,
1::numeric]))
Foreign-key constraints:
    "part_fk_con" FOREIGN KEY (iassigncontainerid) REFERENCES
sam_container(icontainerid)
    "part_fk_fam" FOREIGN KEY (ifamilyid) REFERENCES sam_family(ifamilyid)
    "part_fk_mem" FOREIGN KEY (imemberid) REFERENCES sam_member(imemberid)
    "part_fk_re" FOREIGN KEY (iregeventid) REFERENCES sam_regevent(iregeventid)
    "part_fk_season" FOREIGN KEY (iseasonid) REFERENCES sam_season(iseasonid)
    "part_fk_state" FOREIGN KEY (istateid) REFERENCES sam_state(istateid)
    "sp_fk_m" FOREIGN KEY (imembertypeid) REFERENCES
assoc_membertype(imembertypeid)
Referenced by:
    TABLE "assoc_note" CONSTRAINT "anote_par" FOREIGN KEY
(iparticipantid) REFERENCES sam_participant(iparticipantid)
    TABLE "sam_history" CONSTRAINT "history_fk_part" FOREIGN KEY
(iparticipantid) REFERENCES sam_participant(iparticipantid)
    TABLE "sam_official" CONSTRAINT "off_fk_part" FOREIGN KEY
(iparticipantid) REFERENCES sam_participant(iparticipantid)
    TABLE "sam_reglineitem" CONSTRAINT "rli_fk_part" FOREIGN KEY
(iparticipantid) REFERENCES sam_participant(iparticipantid)
    TABLE "sam_reglineitem" CONSTRAINT "rli_fk_partoff" FOREIGN KEY
(iofficialparticipantid) REFERENCES sam_participant(iparticipantid)
    TABLE "sam_stat" CONSTRAINT "stat_fk_part" FOREIGN KEY
(iparticipantid) REFERENCES sam_participant(iparticipantid)
    TABLE "sam_transfer" CONSTRAINT "transfer_fk_part" FOREIGN KEY
(iparticipantid) REFERENCES sam_participant(iparticipantid)
Triggers:
    "SAM_PARTICIPANT_TRIG1" BEFORE INSERT ON sam_participant FOR EACH
ROW  $trigger$declare
val number(22);
begin
if :new.iParticipantID is null then
        select SAM_Participant_Seq1.nextval into val from dual;
        :new.iParticipantID := val;
end if;
end$trigger$

    playereventtrigger BEFORE INSERT OR UPDATE ON sam_participant FOR
EACH ROW  $trigger$DECLARE

l_newregeventid         NUMBER(22);
l_newseasonid           NUMBER(22);

BEGIN

        IF (TG_OP = 'INSERT' OR :old.iAssignContainerId IS NULL) OR (
:new.iAssignContainerId != :old.iAssignContainerId )
        THEN
        --{
                container_package.findEvent( :new.iAssignContainerId,
l_newregeventid, l_newseasonid, false );
                :new.iregeventid := l_newregeventid;
                :new.iseasonid := l_newseasonid;
        --}
        END IF;

END$trigger$

    samparticipantctimestamp BEFORE INSERT ON sam_participant FOR EACH
ROW  $trigger$BEGIN
        :NEW.dCreatedTimestamp:=SYSTIMESTAMP;
END$trigger$

    samparticipantmtimestamp BEFORE UPDATE ON sam_participant FOR EACH
ROW  $trigger$BEGIN
        :NEW.dModifiedTimestamp:=SYSTIMESTAMP;
END$trigger$


-----------------------

issue I Found out:

 ->  Bitmap Heap Scan on
sam_participant pp  (cost=87058.78..663894.09 rows=2308947 width=10)
(actual time=508.729..4207.342 rows=2335152 loops=1)
                                       Recheck Cond: (igroupid =
((current_setting('env.groupid'::text))::integer)::numeric)
                                       Heap Blocks: exact=387125
                                       Buffers: shared hit=402093



Please suggest  what should I do to reduce the actual time consumed by bitmap
heap scan.(actual time=508.729..4207.342).



Regards,
Atul





[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