From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Anil Menon <gakmenon(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Performance question |
Date: | 2014-11-19 19:46:58 |
Message-ID: | 546CF3B2.5070808@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
> 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.
>
> 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:
It would have to be more like:
perform primary_key_Col from table where <<condition>>
>
> 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.
>
> Thanks in advance
> Anil
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2014-11-19 19:47:42 | Re: [sfpug] Linuxfest 2015 Call for Papers |
Previous Message | Francisco Olarte | 2014-11-19 18:21:00 | Re: Performance question |