Alternative to slow SRF in SELECT?

From: Markur Sens <markursens(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Alternative to slow SRF in SELECT?
Date: 2022-05-17 14:19:51
Message-ID: 0A480F8C-F037-445B-8545-742BB63234D0@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have the following case

select
my_srf_func(otherfunc(h))
from (values (‘…’::mytype),
‘…’::mytype),
(‘…’::mytype),
‘…’::mytype),
(‘…’::mytype),)
as temp(h);

I think it’s obvious what I’m trying to do.

My_srf_func is similar to unnest(array) function.

The problem now is that the result size of this could be around 400-500 elements for every function call of otherfunc(h) so when my_srf_func unnests these I should expect 2K+ items even for this simple case.

It’s also not advised to have SRFs in the SELECT of the query, becuase I do get some unexpected/unintuitive results,
but most importantly it’s way too slow.

Any alternatives on how I could rework the query or the my_srf_func to speed things up, without having too many subqueries?

I guess there should be some kind of pattern there.

If there’s any help, both my_srf_func and otherfunc are coded in PL/Python

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-05-17 14:33:14 Re: Alternative to slow SRF in SELECT?
Previous Message huangning290@yahoo.com 2022-05-17 12:24:18 Parallel not working