Is there a way to improve the query performance in postgresql?

Soundar 0
Viewed 41

postgresql:

I'm writing a query in postgresql which is getting struck while running. It is not at all returning any records. Could anyone help me on this?

Actual Query:

select a.auditdate,b.description as auditcategory,remoteaddress,u.name as user1,e.name || '[' || e.employeenumber +']' as employee,a.additionalinfo from tblauditlog a
    inner join tblauditcategory b on b.cid = a.auditcategory and b.cid<>756
    left outer join tbluser u on a.userid=u.cid and u.usertype<>250
    left outer join tblemployee e on (a.affectedemployeeid=e.cid or a.affectedemployee=e.cid or a.affectedemployee=e.employeenumber)
    where auditdate >= '01 sep 2022' and auditdate <= '15 sep 2022' order by a.auditdate desc

Query Plan:

Nested Loop Left Join  (cost=0.71..659969657.09 rows=1026362 width=151)
   Join Filter: (a.userid = u.cid)
   ->  Nested Loop Left Join  (cost=0.71..654861596.89 rows=1026362 width=142)
         Join Filter: ((a.affectedemployeeid = e.cid) OR ((a.affectedemployee)::text = textin(int4out(e.cid))) OR ((a.affectedemployee)::text = (e.employeenumber)::text))
         ->  Nested Loop  (cost=0.71..268627.45 rows=566679 width=134)
               ->  Index Scan Backward using idx_tblauditlog_auditdate on tblauditlog a  (cost=0.43..101251.69 rows=567370 width=112)
                     Index Cond: ((auditdate >= '2022-09-01 00:00:00'::timestamp without time zone) AND (auditdate <= '2022-09-15 00:00:00'::timestamp without time zone))
               ->  Index Scan using tblauditcategory_pkey on tblauditcategory b  (cost=0.28..0.30 rows=1 width=30)
                     Index Cond: (cid = a.auditcategory)
                     Filter: (cid <> 756)
         ->  Materialize  (cost=0.00..8005.07 rows=46205 width=33)
               ->  Seq Scan on tblemployee e  (cost=0.00..7774.05 rows=46205 width=33)
   ->  Materialize  (cost=0.00..4554.94 rows=331 width=14)
         ->  Seq Scan on fk_tbluser u  (cost=0.00..4553.29 rows=331 width=14)
               Filter: (usertype <> '250'::numeric)
(15 rows)

Actual number of records in each table:

tblAuditlog : 6852333
tblAuditCategory : 825
tbluser : 46342
tblemployee : 46014

Index created:

tblAuditlog:
    "tblauditlog_pkey" PRIMARY KEY, btree (cid)
    "idx_tblauditlog_auditdate" btree (auditdate, auditcategory, userid)

tblAuditcategory:
    1. "tblauditcategory_pkey" PRIMARY KEY, btree (cid)
    2. "tblauditCategory_unique" UNIQUE CONSTRAINT, btree (code)
    3. "idx_tblauditcategory_code" btree (code)

tbluser:
    1. "tbluser_pkey" PRIMARY KEY, btree (cid)
    2. "tbluser_employeeid_key" UNIQUE CONSTRAINT, btree (employeeid)
    3. "tbluser_name_key" UNIQUE CONSTRAINT, btree (name)
    4. "uq_fk_tbluser_name_type_employeeid" UNIQUE CONSTRAINT, btree (name, usertype, employeeid)
    5. "idx_tbluser_utype" btree (cid, usertype)

tblemployee:
    1. "tblemployee_pkey" PRIMARY KEY, btree (cid)
    2. "tblemployee_employeeno_key" UNIQUE CONSTRAINT, btree (employeenumber)
    3. "tblemployee_guid_key" UNIQUE CONSTRAINT, btree (sid)
    4. "idx_tblemployee_employeeno" btree (employeenumber)

Thanks in advance...

postgresql
1 Answers

Thanks @jjanes. As you suggested the OR condition was the culprit. I have changed the query like this. After the modification, it is getting executed within a fraction of second. Thank you all for your support.

The modified query is:

select a.auditdate,b.description as auditcategory,remoteaddress,u.name as user1,coalesce(e.name,e1.name,e2.name) || '[' || coalesce(e.employeeno,e1.employeeno,e2.employeeno) +']' as employee,a.additionalinfo from tblauditlog a
inner join tblauditcategory b on b.cid = a.auditcategory and b.cid<>756
left outer join tbluser u on a.userid=u.cid and u.usertype<>250
left outer join tblemployee e on (a.affectedemployeeid=e.cid) 
left join tblemployee e1 on(a.affectedemployee=e1.cid) 
left join tblemployee e2 on(a.affectedemployee=e2.employeeno)
where auditdate >= '01 sep 2022' and auditdate <= '15 sep 2022'  order by a.auditdate desc ;

There is a huge improvement in the query plan:

 Sort  (cost=433884.73..436109.96 rows=890089 width=151)
   Sort Key: a.auditdate DESC
   ->  Hash Join  (cost=252015.26..306723.76 rows=890089 width=151)
         Hash Cond: (a.auditcategory = b.cid)
         ->  Merge Left Join  (cost=251985.75..297667.18 rows=891175 width=184)
               Merge Cond: ((a.affectedemployee)::text = (e2.employeeno)::text)
               ->  Merge Left Join  (cost=251985.33..275914.91 rows=891175 width=172)
                     Merge Cond: ((a.affectedemployee)::text = (textin(int4out(e1.cid))))
                     ->  Sort  (cost=227637.20..229094.12 rows=582767 width=143)
                           Sort Key: a.affectedemployee
                           ->  Hash Left Join  (cost=20079.20..147328.20 rows=582767 width=143)
                                 Hash Cond: (a.userid = u.cid)
                                 ->  Hash Left Join  (cost=15491.10..141210.24 rows=582767 width=137)
                                       Hash Cond: (a.affectedemployeeid = e.cid)
                                       ->  Index Scan Backward using idx_tblauditlog_auditdate on tblauditlog a  (cost=0.43..103968.76 rows=582767 width=112)
                                             Index Cond: ((auditdate >= '2022-09-01 00:00:00'::timestamp without time zone) AND (auditdate <= '2022-09-15 00:00:00'::timestamp without time zone))
                                       ->  Hash  (cost=13227.63..13227.63 rows=111363 width=33)
                                             ->  Seq Scan on tblemployee e  (cost=0.00..13227.63 rows=111363 width=33)
                                 ->  Hash  (cost=4583.89..4583.89 rows=337 width=14)
                                       ->  Seq Scan on tbluser u  (cost=0.00..4583.89 rows=337 width=14)
                                             Filter: (usertype <> '250'::numeric)
                     ->  Materialize  (cost=24348.13..24904.95 rows=111363 width=33)
                           ->  Sort  (cost=24348.13..24626.54 rows=111363 width=33)
                                 Sort Key: (textin(int4out(e1.cid)))
                                 ->  Seq Scan on tblemployee e1  (cost=0.00..13227.63 rows=111363 width=33)
               ->  Index Scan using tblemployee_employeeno_key on tblemployee e2  (cost=0.42..15775.39 rows=111363 width=29)
         ->  Hash  (cost=19.26..19.26 rows=820 width=30)
               ->  Seq Scan on tblauditcategory b  (cost=0.00..19.26 rows=820 width=30)
                     Filter: (cid <> 756)
(29 rows)

Thank you once again...

Soundar0
Related