postgresql : Same raw query run faster than in function

Question

Same query run faster outside of function but in function it runs hundred times slower and when I remove where clause it runs faster in function too but not faster than raw query. I wonder whats the problem.

RETURN QUERY (SELECT 
    t.tran_id,  t.tran_date,  t.value_date, t.statement_reference, t.debit,   t.credit,t.office_id
FROM transactions.transactions_view t
WHERE t.tran_date BETWEEN from_::date AND to_::date

 AND t.gl_account_id = gl_account_id_);

Daxelarne · Accepted Answer · 2022-12-09 10:34:30Z

2

You should try with:

RETURN QUERY EXECUTE (SELECT 
    t.tran_id,  t.tran_date,  t.value_date, t.statement_reference, t.debit, t.credit,t.office_id
FROM transactions.transactions_view t
WHERE t.tran_date BETWEEN from_::date AND to_::date
 AND t.gl_account_id = gl_account_id_);

See RETURN QUERY vs RETURN QUERY EXECUTE and PostgreSQL: Documentation

answered Dec 9, 2022 at 10:34

Daxelarne

17813 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Luuk Over a year ago

Reading this comment: Nice explanation. Actually, the second one (with EXECUTE) takes much more time if the data volume is increased. This problem I fetched one of my LIVE project having millions of users. For first one types of query take <200 ms whereas the second type of query takes 14 seconds the real answer might be (at least a bit) more complex.

Jeevan Gharti Over a year ago

later when I was checking the query I found that there is problem with one date range when I pass the date between '2022-10-29' AND '2022-11-20' then it works fine and it generates result within 2 sec but when I change the date range between '2022-10-29' AND '2022-11-20' it takes more than 8 minutes to generate the results.

Collectives™ on Stack Overflow

postgresql : Same raw query run faster than in function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related