Optimizing PgSQL Query function performance

[kevinpg18]

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.

CREATE OR REPLACE FUNCTION "sample"."getpreviousorders"("_sampleid" varchar, "_mrn" varchar, "_createddate" timestamp)
    RETURNS SETOF "sample"."order" AS $BODY$
BEGIN
    RETURN QUERY
        SELECT o1.* FROM "sample"."order" o1 JOIN "sample".patient p1 ON o1.patient_id = p1.id
        WHERE o1.sample_id != _sampleId AND p1.mrn = _mrn
          AND ((o1.collection_date is not null AND o1.collection_date >= _createdDate)
            OR (o1.collection_date is null AND o1.receipt_date is not null
                AND o1.receipt_date >= _createdDate))
        UNION
        (SELECT o2.* FROM "sample"."order" o2 JOIN "sample".patient p2 ON o2.patient_id = p2.id
         WHERE (o2.status = 1 or o2.status = 2 OR o2.status = 3) AND o2.sample_id != _sampleId
         AND p2.mrn = _mrn
           AND ((o2.collection_date is not null AND o2.collection_date < _createdDate)
             OR (o2.collection_date is null AND o2.receipt_date is not null
                 AND o2.receipt_date < _createdDate)) ORDER by created_date DESC LIMIT 1)
        ORDER by collection_date DESC, receipt_date DESC, created_date DESC;
END; $BODY$
    LANGUAGE plpgsql VOLATILE
                     COST 100
                     ROWS 1000

[Banfa]

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.

[Chandler]

I would suggest
Step 1: rewrite the query body to

        SELECT o1.* FROM "sample"."order" o1 JOIN "sample".patient p1 ON o1.patient_id = p1.id
        WHERE o1.sample_id != _sampleId AND p1.mrn = _mrn
          AND o1.collection_date is not null AND o1.collection_date >= _createdDate
 
        union 
 
        SELECT o1.* FROM "sample"."order" o1 JOIN "sample".patient p1 ON o1.patient_id = p1.id
        WHERE o1.sample_id != _sampleId AND p1.mrn = _mrn
          AND o1.collection_date is null AND o1.receipt_date is not null
                AND o1.receipt_date >= _createdDate
 
        UNION
 
        (
        SELECT o2.* FROM "sample"."order" o2 JOIN "sample".patient p2 ON o2.patient_id = p2.id
         WHERE o2.status in(1,2,3) AND o2.sample_id != _sampleId
         AND p2.mrn = _mrn
           AND o2.collection_date is not null AND o2.collection_date < _createdDate
           UNION ALL
           SELECT o2.* FROM "sample"."order" o2 JOIN "sample".patient p2 ON o2.patient_id = p2.id
         WHERE o2.status in(1,2,3) AND o2.sample_id != _sampleId
         AND p2.mrn = _mrn and o2.collection_date is null AND o2.receipt_date is not null
                 AND o2.receipt_date < _createdDate)
 
                 ORDER by created_date DESC LIMIT 1)
 
        ORDER by collection_date DESC, receipt_date DESC, created_date DESC;
 

Step2. use index advise tool for pg(such as Paw Index Advisor) to accelerate the performance.