Performance question

From: Anil Menon <gakmenon(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Performance question
Date: 2014-11-19 16:26:23
Message-ID: CAHzbRKc_=d8HeJ=w7ymCRHqxMUHK6xWe57Jm0WPb=_XyKqb15Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

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

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)

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?

Thanks in advance
Anil

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jonathan Vanasco 2014-11-19 16:54:33 Re: issue with double ordering in a wrapped distinct
Previous Message Adrian Klaver 2014-11-19 15:06:26 Re: postgresql for small business