Re: optimization issue

Hi,

Could you suggest me how should I teduce the heap blocks to optimise the
query ?

Regards.

On Thursday, July 8, 2021, 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)
>
>
>
>
>
> 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
>