Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010

    Unanswered: Sub Query - performance issue

    Hello Gurus,

    I am quiet new to postgres and I am trying to run below subquery which seems to take forever - could you help me? I have given some details below.

    "select factxn,facptype,facsentdate,faccur,facsendinst, refnumf, codest from facreceive where
    facptype = '10' and factxn not in (select txn from sjrnl where direction='INWARD' and ptype = '10');"

    Some stats on table:
    select count(*) from facreceive; 79130
    select count(*) from sjrnl; 169563
    select count(*) from facreceive where facptype = '10'; 41604
    select count(*) from sjrnl where direction = 'INWARD' and ptype = '10'; 41602

    Table Definitions:

    CREATE TABLE sjrnl
    direction character(7),
    txn character(18) NOT NULL,
    ptype character(2) NOT NULL,
    sentdate character(8) NOT NULL,
    fpidcur character(3) NOT NULL,
    sendinst character(6) NOT NULL,
    status character(40),
    amount numeric(10,2),
    rfpid character(37),
    CONSTRAINT sjrnl_pkey PRIMARY KEY (txn, ptype, sentdate, fpidcur, sendinst)
    WITH (
    ALTER TABLE sjrnl OWNER TO xx;

    CREATE TABLE facreceive
    factxn character(18) NOT NULL,
    facptype character(2) NOT NULL,
    facsentdate character(8) NOT NULL,
    faccur character(3) NOT NULL,
    facsendinst character(6) NOT NULL,
    refnumf character(10),
    codest character(3),
    CONSTRAINT facreceive_pkey PRIMARY KEY (factxn, facptype, facsentdate, faccur, facsendinst)
    WITH (
    ALTER TABLE facreceive OWNER TO xx;

    Explain gives below output:

    "Index Scan using factype_index on facreceive (cost=0.00..132378527.65 rows=20827 width=57)"
    " Index Cond: (facptype = '10'::bpchar)"
    " Filter: (NOT (SubPlan 1))"
    " SubPlan 1"
    " -> Materialize (cost=0.00..6290.46 rows=26203 width=19)"
    " -> Seq Scan on sjrnl (cost=0.00..6005.44 rows=26203 width=19)"
    " Filter: ((direction = 'INWARD'::bpchar) AND (ptype = '10'::bpchar))"

  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    Looks like an index on sjrnl, columns direction and ptype may help as you are scanning entire table right now.
    Also, turning the logic around may help as well instead of
    factxn not in (select txn from sjrnl where direction='INWARD' and ptype = '10');"
    you might try:
    factxn in (select txn from sjrnl where direction <> 'INWARD' and ptype <> '10');"
    If you have the index columns on the facreceive table as facptype and factxn.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts