0

I'm facing a performance problem while joining a table with a view that has UDF in its definition. Although the documentation says that running a query against a view is just like running the SQL statement directly, in my case, the execution plan for the query using the view versus the same query with the view code is totally different. When I run a join with the view, it fetches its data first and then applies filters. This process is quite slow as it returns more than 4M rows.

I've copied its code and executed the query, so the time has been reduced from 30 to 0.6 seconds. Here is an example:


create view myview1 as 
select t.ticketid
  from tickets t 
create view myview2 as 
select t.ticketid, 
     getminviolationdate(t.slaviolationtimeclosed::timestamp without time zone, t.slaviolationlastaction::timestamp without time zone, t.slaviolationinitialresponse::timestamp without time zone) AS "SlaViolationHours"
  from tickets t 

Explain Analyze
select * from myview1 t
inner join ForumTickets ftt1 on t.TicketID = ftt1.ticketid
  where t.ticketID in (select ticketid from actions where CreatorID = 1)    
"Nested Loop Semi Join  (cost=0.99..1018.95 rows=1 width=16) (actual time=15.402..15.403 rows=0 loops=1)"
"  ->  Nested Loop  (cost=0.43..564.38 rows=344 width=16) (actual time=0.022..1.536 rows=344 loops=1)"
"        ->  Seq Scan on forumtickets ftt1  (cost=0.00..5.44 rows=344 width=12) (actual time=0.009..0.051 rows=344 loops=1)"
"        ->  Index Only Scan using ix_tickets_ticketidandticketsta903138f3a353605aaaefa9c383792122 on tickets t  (cost=0.43..1.62 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=344)"
"              Index Cond: (ticketid = ftt1.ticketid)"
"              Heap Fetches: 227"
"  ->  Index Only Scan using covix_actions_reportdetailsbyti0b5314e1e76e5800fdee45faa2624a58 on actions  (cost=0.56..1.32 rows=1 width=4) (actual time=0.040..0.040 rows=0 loops=344)"
"        Index Cond: ((ticketid = t.ticketid) AND (creatorid = 1))"
"        Heap Fetches: 0"
"Planning Time: 1.703 ms"
"Execution Time: 15.444 ms"



explain analyze
  select * from myview2 t
inner join ForumTickets ftt1 on t.TicketID = ftt1.ticketid
  where t.ticketID in (select ticketid from actions where CreatorID = 257660)   

Execution Plan:


"Nested Loop Semi Join  (cost=10.30..1236967.29 rows=3 width=24)"
"  ->  Hash Join  (cost=9.74..1236513.45 rows=344 width=24)"
"        Hash Cond: (t.ticketid = ftt1.ticketid)"
"        ->  Seq Scan on tickets t  (cost=0.00..1183755.92 rows=4178042 width=12)"
"        ->  Hash  (cost=5.44..5.44 rows=344 width=12)"
"              ->  Seq Scan on forumtickets ftt1  (cost=0.00..5.44 rows=344 width=12)"
"  ->  Index Only Scan using covix_actions_reportdetailsbyti0b5314e1e76e5800fdee45faa2624a58 on actions  (cost=0.56..1.32 rows=1 width=4)"
"        Index Cond: ((ticketid = t.ticketid) AND (creatorid = 257660))"

Function Code:

CREATE OR REPLACE FUNCTION getminviolationdate(
    par_value1 timestamp without time zone,
    par_value2 timestamp without time zone,
    par_value3 timestamp without time zone)
    RETURNS timestamp without time zone
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

/* ------------------------------- */
/* Funcs: */
DECLARE
    var_result TIMESTAMP WITHOUT TIME ZONE;
    var_MaxDate TIMESTAMP WITHOUT TIME ZONE;
BEGIN
    var_MaxDate := '1/1/2200';
    SELECT
        COALESCE(par_Value1, var_MaxDate)
        INTO var_result;

    IF (var_result > COALESCE(par_Value2, var_MaxDate)) THEN
        SELECT
            COALESCE(par_Value2, var_MaxDate)
            INTO var_result;
    END IF;

    IF (var_result > COALESCE(par_Value3, var_MaxDate)) THEN
        SELECT
            COALESCE(par_Value3, var_MaxDate)
            INTO var_result;
    END IF;

    IF (var_result >= var_MaxDate) THEN
        RETURN NULL;
    END IF;
    RETURN var_result;
END;
$BODY$;

Table info: ticketid is the primary key of the ticket table; Table actions has indexes on 3 columns: actionId (PK), ticketID and CreatorID.

Does anyone know if there is a way to overcome that? Thank you! Hander

  • A simple EXPLAIN plan actually provides very little information. Please update your question and replace the current simple EXPLAIN result with the results of EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT TEXT) on the queries. Additionally post the function `getminviolationdate()` and the table definitions (ddl) for tables with any indexes defined. – Belayer Jun 08 '23 at 18:21

0 Answers0