471,049 Members | 1,466 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

Optimizing PgSQL Query function performance

I really need advice on the below, trying to use DB function getpreviousorders..

NOTE: But this DB function got much faster if run directly on DB, without data reference entity data by spring hibernate code in Order entity.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION "sample"."getpreviousorders"("_sampleid" varchar, "_mrn" varchar, "_createddate" timestamp)
  2.     RETURNS SETOF "sample"."order" AS $BODY$
  3. BEGIN
  4.     RETURN QUERY
  5.         SELECT o1.* FROM "sample"."order" o1 JOIN "sample".patient p1 ON o1.patient_id = p1.id
  6.         WHERE o1.sample_id != _sampleId AND p1.mrn = _mrn
  7.           AND ((o1.collection_date is not null AND o1.collection_date >= _createdDate)
  8.             OR (o1.collection_date is null AND o1.receipt_date is not null
  9.                 AND o1.receipt_date >= _createdDate))
  10.         UNION
  11.         (SELECT o2.* FROM "sample"."order" o2 JOIN "sample".patient p2 ON o2.patient_id = p2.id
  12.          WHERE (o2.status = 1 or o2.status = 2 OR o2.status = 3) AND o2.sample_id != _sampleId
  13.          AND p2.mrn = _mrn
  14.            AND ((o2.collection_date is not null AND o2.collection_date < _createdDate)
  15.              OR (o2.collection_date is null AND o2.receipt_date is not null
  16.                  AND o2.receipt_date < _createdDate)) ORDER by created_date DESC LIMIT 1)
  17.         ORDER by collection_date DESC, receipt_date DESC, created_date DESC;
  18. END; $BODY$
  19.     LANGUAGE plpgsql VOLATILE
  20.                      COST 100
  21.                      ROWS 1000
Jul 12 '21 #1
2 9446
Banfa
9,065 Expert Mod 8TB
Since the SELECT ... FROM ... part of both halves of the union are the same can you not just use a single select statement that combines the 2 where clauses (into a hideously large clause). I think that will result in not having to process every record twice, once for each SELECT.
Jul 21 '21 #2
Chandler
1 Bit
I would suggest
Step 1: rewrite the query body to
Expand|Select|Wrap|Line Numbers
  1.         SELECT o1.* FROM "sample"."order" o1 JOIN "sample".patient p1 ON o1.patient_id = p1.id
  2.         WHERE o1.sample_id != _sampleId AND p1.mrn = _mrn
  3.           AND o1.collection_date is not null AND o1.collection_date >= _createdDate
  4.  
  5.         union 
  6.  
  7.         SELECT o1.* FROM "sample"."order" o1 JOIN "sample".patient p1 ON o1.patient_id = p1.id
  8.         WHERE o1.sample_id != _sampleId AND p1.mrn = _mrn
  9.           AND o1.collection_date is null AND o1.receipt_date is not null
  10.                 AND o1.receipt_date >= _createdDate
  11.  
  12.         UNION
  13.  
  14.         (
  15.         SELECT o2.* FROM "sample"."order" o2 JOIN "sample".patient p2 ON o2.patient_id = p2.id
  16.          WHERE o2.status in(1,2,3) AND o2.sample_id != _sampleId
  17.          AND p2.mrn = _mrn
  18.            AND o2.collection_date is not null AND o2.collection_date < _createdDate
  19.            UNION ALL
  20.            SELECT o2.* FROM "sample"."order" o2 JOIN "sample".patient p2 ON o2.patient_id = p2.id
  21.          WHERE o2.status in(1,2,3) AND o2.sample_id != _sampleId
  22.          AND p2.mrn = _mrn and o2.collection_date is null AND o2.receipt_date is not null
  23.                  AND o2.receipt_date < _createdDate)
  24.  
  25.                  ORDER by created_date DESC LIMIT 1)
  26.  
  27.         ORDER by collection_date DESC, receipt_date DESC, created_date DESC;
  28.  
Step2. use index advise tool for pg(such as Paw Index Advisor) to accelerate the performance.
4 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by gord barq | last post: by
3 posts views Thread by Alexander Anderson | last post: by
18 posts views Thread by Ron Johnson | last post: by
1 post views Thread by frizzle | last post: by
1 post views Thread by mamapossible | last post: by
1 post views Thread by thoolsur | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.