Postgres Pro
Downloads
Home   >   mailing lists

Thread: Best way to know if there is a row

Best way to know if there is a row

From
Martin Marques
Date:
I have a bunch of queries in a system I'm finishing, and I bumped with a
question on performace.
Which is the best way to solve this:

I need to know if there is at least one row in the relation that comes from a
determinated query. Which is the best way to do this:

(a) Build the query with "SELECT * ..." and after executing the query see if
numRows()>0
(b) Build the query with "SELECT count(*) ...", fetch the row and see if
count>0

I'm working with (a) because I see it better in performace, but I wanted to be
sure the numRows() will actually give me the exact amount of rows (had some
problems in the past with Informix).

The aplication is written in PHP.

-- 09:45:02 up 16 days, 3 min,  4 users,  load average: 3.32, 2.69, 1.77
-----------------------------------------------------------------
Martín Marqués        | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica  |  DBA, Programador, Administrador            Universidad Nacional                 del Litoral
-----------------------------------------------------------------

Re: Best way to know if there is a row

From
Achilleus Mantzios
Date:
O Martin Marques έγραψε στις Sep 28, 2004 :

> I have a bunch of queries in a system I'm finishing, and I bumped with a 
> question on performace.
> Which is the best way to solve this:
> 
> I need to know if there is at least one row in the relation that comes from a 
> determinated query. Which is the best way to do this:
> 
> (a) Build the query with "SELECT * ..." and after executing the query see if 
> numRows()>0
> (b) Build the query with "SELECT count(*) ...", fetch the row and see if 
> count>0

You could also try
(c) SELECT exists (select 1 ... limit 1) and test the boolean value
in the one and only row returned, where "..." is your clause
as in (a),(b).

> 
> I'm working with (a) because I see it better in performace, but I wanted to be 
> sure the numRows() will actually give me the exact amount of rows (had some 
> problems in the past with Informix).
> 
> The aplication is written in PHP.
> 
> 

-- 
-Achilleus

Re: Best way to know if there is a row

From
Tom Lane
Date:
Martin Marques <martin@bugs.unl.edu.ar> writes:
> I need to know if there is at least one row in the relation that comes from a 
> determinated query. Which is the best way to do this:

> (a) Build the query with "SELECT * ..." and after executing the query see if 
> numRows()>0
> (b) Build the query with "SELECT count(*) ...", fetch the row and see if 
> count>0

Either of these implies computing the entire query result set, which is
much more computation than you want.  Instead doSELECT * .... LIMIT 1
and then see if you got a row or not.  Aside from not computing useless
rows, the LIMIT will bias the optimizer in favor of fast-start plans.
        regards, tom lane