Thanks Adrian
On Thu, Nov 20, 2014 at 3:46 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 11/19/2014 08:26 AM, Anil Menon wrote: > >> Hello, >> >> 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: > > http://www.postgresql.org/docs/9.3/interactive/plpgsql- > statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT > > It would have to be more like: > > 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. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > I guess the best answer would be "its very context specific", but picking the brains of experienced resources helps :-) Thanks again Anil
