Search Postgresql Archives

Re:

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> wrote:
> Hi,
>
> On Fri, Jul 2, 2021, 5:24 AM 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)
>>
>>
>>
>>
>>
>> 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.
>>
>>
>>
>





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux