pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Best way to know if there is a row
3+ messages / 3 participants
[nested] [flat]
* Re: Best way to know if there is a row
@ 2004-09-28 12:24 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Martin Marques <martin@bugs.unl.edu.ar>
1 sibling, 0 replies; 3+ messages in thread
From: Achilleus Mantzios @ 2004-09-28 12:24 UTC (permalink / raw)
To: Martin Marques <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Best way to know if there is a row
@ 2004-09-28 12:55 Martin Marques <martin@bugs.unl.edu.ar>
0 siblings, 2 replies; 3+ messages in thread
From: Martin Marques @ 2004-09-28 12:55 UTC (permalink / raw)
To: pgsql-sql
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
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best way to know if there is a row
@ 2004-09-28 14:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Martin Marques <martin@bugs.unl.edu.ar>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-09-28 14:24 UTC (permalink / raw)
To: Martin Marques <martin@bugs.unl.edu.ar>; +Cc: pgsql-sql
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 do
SELECT * .... 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-09-28 14:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-28 12:55 Best way to know if there is a row Martin Marques <martin@bugs.unl.edu.ar>
2004-09-28 12:24 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-09-28 14:24 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox