Re: - Mailing list pgsql-general
| From | Atul Kumar |
|---|---|
| Subject | Re: |
| Date | |
| Msg-id | CA+ONtZ4i5WEGUP2fC6ion=RdAPKZm0Svcp7_BLVXLAO9d8Sw9A@mail.gmail.com Whole thread Raw |
| In response to | Re: (Igor Korot <ikorot01@gmail.com>) |
| List | pgsql-general |
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
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=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.
>>
>
> Could you please show the tables schema involved?
>
> Thank you.
>
>
>>
>>
>> Regards.
>>
>>
>>
>
pgsql-general by date: