Thread: optimization issue
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
((current_setting('env.groupid'::text))::integer)::numeric
If you pass this value in directly as part of the query string, how does it perform? It seems like calling the function to get this value may be creating a planning problem with the value unknown at plan time. If you were able to put the result on an analyzed temp table at least, I'd expect that the planner would have enough info to make good decisions.
Hi Lewis,
On Thursday, July 8, 2021, Michael Lewis <mlewis@entrata.com> wrote:
I am new to postgres.
Could you tell me how should I put the result on an analyzed temp table at least ?
Please suggest.
Regards.
On Thursday, July 8, 2021, Michael Lewis <mlewis@entrata.com> wrote:
((current_setting('env.groupid'::text))::integer):: numeric If you pass this value in directly as part of the query string, how does it perform? It seems like calling the function to get this value may be creating a planning problem with the value unknown at plan time. If you were able to put the result on an analyzed temp table at least, I'd expect that the planner would have enough info to make good decisions.
Hello, I am curious. NVL, DECODE and SELECT FROM dual are Oracle methods and these appear in your code. How did you make these work in Postgres? Cheers, Rob
On Fri, Jul 09, 2021 at 01:16:16PM +1000, rob stone wrote: > Hello, > > I am curious. > > NVL, DECODE and SELECT FROM dual are Oracle methods and these appear in > your code. > > How did you make these work in Postgres? > > Cheers, > Rob Hi Rob, At a guess, they are using the Orafce PostgreSQL extension. It provides a lot of compatibility functions. Regards, Ken
Hello Ken, On Fri, 2021-07-09 at 13:27 -0500, Kenneth Marshall wrote: > On Fri, Jul 09, 2021 at 01:16:16PM +1000, rob stone wrote: > > Hello, > > > > I am curious. > > > > NVL, DECODE and SELECT FROM dual are Oracle methods and these > > appear in > > your code. > > > > How did you make these work in Postgres? > > > > Cheers, > > Rob > > Hi Rob, > > At a guess, they are using the Orafce PostgreSQL extension. It > provides > a lot of compatibility functions. > > Regards, > Ken Thanks. Found it on github. Regards, Rob
Hi,
On Thursday, July 8, 2021, Atul Kumar <akumar14871@gmail.com> wrote:
Could you suggest me how should I teduce the heap blocks to optimise the query ?
Regards.
On Thursday, July 8, 2021, Atul Kumar <akumar14871@gmail.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