I would like to ask from your experience which would be the best "generic" method for checking if row sets of a certain condition exists in a PLPGSQL function.
I know of 4 methods so far (please feel free to add if I missed out any others)
1) get a count (my previous experience with ORCL shaped this option)
select count(*) into vcnt from table where <<condition>> if vcnt >0 then do X else do y end if Cons : It seems doing a count(*) is not the best option for PG
Well that would depend on the table size, whether it was 100 rows vs 1,000,000 rows
The table is estimated/guesstimated to be ~900 million rows (~30Ma day, 90 days history, though initially it would be ~30M), though the <<where>> part of the query would return between 0 and ~2 rows
2) Use a non-count option select primary_key_Col into vcnt from table where <<condition>> if found then do X else do y end if Cons :Some people seems not to prefer this as (AFAIU) it causes a plpgsql->sql->plpgsql switches
plpgsql is fairly tightly coupled to SQL, so I have not really seen any problems. But then I am not working on large datasets.
I think that ~900M rows would constitute a large data set most likely
3) using perform perform primary_key_Col into vcnt from table where <<condition>> if found then do X else do y end if
Seems to remove the above (item 2) issues (if any)
AFAIK, you cannot do the above as written. PERFORM does not return a result:
perform primary_key_Col from table where <<condition>>
You are absolutely right - my bad.
4) using exists if exists ( select 1 from table where <<condition>> ) then do x else do y end if
My question is what would be the best (in terms of performance) method to use? My gut feeling is to use option 4 for PG. Am I right or is there any other method?
All of the above is context specific. To know for sure you will need to test on actual data.
Absolutely right, just that I want to ensure that I follow the most optimal method before the DB goes into production, after which priorities change on what needs to be changed.