Could you suggest me how should I teduce the heap blocks to optimise the query ?
Regards.
On Thursday, July 8, 2021, 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)
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
