No title
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.
Hi,
On Fri, Jul 2, 2021, 5:24 AM 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)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=524884I 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.
Could you please show the tables schema involved?
Thank you.
Show quoted text
Regards.
Igor Korot <ikorot01@gmail.com> writes:
On Fri, Jul 2, 2021, 5:24 AM Atul Kumar <akumar14871@gmail.com> wrote:
-> 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)
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.
Where is that cast to integer coming from? That's likely causing
the WHERE clause to not match your index. What's the actual type
of the igroupid column?
regards, tom lane
Hi,
Below is given 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$
Note: we have created index on column igroupid.
Regards,
Atul
Show quoted text
On 7/2/21, Igor Korot <ikorot01@gmail.com> wrote:
Hi,
On Fri, Jul 2, 2021, 5:24 AM 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)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=524884I 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.
Could you please show the tables schema involved?
Thank you.
Regards.
Hi,
I have created index on igroupid column, below is the structure and
new explain plan give:
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$
New explain plan :
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$
Please what should I do to reduce the actual time consumed by bitmap
heap scan.(actual time=508.729..4207.342).
Regards,
Atul
Show quoted text
On 7/2/21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Igor Korot <ikorot01@gmail.com> writes:
On Fri, Jul 2, 2021, 5:24 AM Atul Kumar <akumar14871@gmail.com> wrote:
-> 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)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.Where is that cast to integer coming from? That's likely causing
the WHERE clause to not match your index. What's the actual type
of the igroupid column?regards, tom lane