pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFetch a single record
4+ messages / 4 participants
[nested] [flat]
* Fetch a single record
@ 2003-12-05 21:51 David Shadovitz <david_shadovitz@xontech.com>
2003-12-09 20:21 ` Re: Fetch a single record Peter Eisentraut <peter_e@gmx.net>
2003-12-10 00:11 ` Re: Fetch a single record Paul Thomas <paul@tmsl.demon.co.uk>
2003-12-10 00:45 ` Re: Fetch a single record greg@turnstep.com
0 siblings, 3 replies; 4+ messages in thread
From: david_shadovitz@xontech.com @ 2003-12-05 21:51 UTC (permalink / raw)
To: pgsql-sql
I'm looking for the fastest way to fetch a single record from a table.
I don't care what record it is.
Here are two techniques I've explored:
1. Use LIMIT
SELECT * FROM myTable LIMIT 1
2. Get a valid OID and then get the record.
SELECT MIN(oid) AS anOID FROM myTable
SELECT * FROM myTable WHERE oid = anOID
The 1st technique is slow. (I think PostgreSQL fetches all records
and then returns just one.) The 2nd is faster, but not fast enough.
Any other ideas? Please reply to me personally (david@shadovitz.com)
as well as to the list.
Thanks.
-David
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fetch a single record
2003-12-05 21:51 Fetch a single record David Shadovitz <david_shadovitz@xontech.com>
@ 2003-12-09 20:21 ` Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 4+ messages in thread
From: Peter Eisentraut @ 2003-12-09 20:21 UTC (permalink / raw)
To: david_shadovitz@xontech.com; pgsql-sql
David Shadovitz wrote:
> I'm looking for the fastest way to fetch a single record from a
> table. I don't care what record it is.
>
> Here are two techniques I've explored:
>
> 1. Use LIMIT
> SELECT * FROM myTable LIMIT 1
>
> 2. Get a valid OID and then get the record.
> SELECT MIN(oid) AS anOID FROM myTable
> SELECT * FROM myTable WHERE oid = anOID
>
> The 1st technique is slow. (I think PostgreSQL fetches all records
> and then returns just one.) The 2nd is faster, but not fast enough.
This is hard to believe. The first technique should be the fasted, but
might suffer from a bad plan. Please make sure that you have run
ANALYZE, and then post EXPLAIN ANALYZE output.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fetch a single record
2003-12-05 21:51 Fetch a single record David Shadovitz <david_shadovitz@xontech.com>
@ 2003-12-10 00:11 ` Paul Thomas <paul@tmsl.demon.co.uk>
2 siblings, 0 replies; 4+ messages in thread
From: Paul Thomas @ 2003-12-10 00:11 UTC (permalink / raw)
To: David Shadovitz <david_shadovitz@xontech.com>; +Cc: pgsql-sql
On 05/12/2003 21:51 David Shadovitz wrote:
> I'm looking for the fastest way to fetch a single record from a table.
> I don't care what record it is.
> [snip]
Have you also tried
SELECT * from mytable limit 1
If you genuinely don't care what the record is (I assume you're justing
testing that table is not empty?) then this might be the way to go. My
rather limited knowledge of PG internals leads me to believe that this
will generally cause just one page being read from disk (I'm assuming the
99% case of no mega-sized text/bytea/whatever columns here). I'd be
interested to know just how far off the mark by understanding is...
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Fetch a single record
2003-12-05 21:51 Fetch a single record David Shadovitz <david_shadovitz@xontech.com>
@ 2003-12-10 00:45 ` greg@turnstep.com
2 siblings, 0 replies; 4+ messages in thread
From: greg@turnstep.com @ 2003-12-10 00:45 UTC (permalink / raw)
To: pgsql-sql; +Cc: david_shadovitz@xontech.com
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> I'm looking for the fastest way to fetch a single record from a table.
> I don't care what record it is.
That's a strange request. If you really don't care what comes back, you
don't even need to query a table:
SELECT 1;
If you perhaps want the column names, query the system tables.
Otherwise the LIMIT 1 should be very fast, especially if you have no
ordering. Avoid the "SELECT *" if you don't need all the columns
for a little more speed.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200312091943
-----BEGIN PGP SIGNATURE-----
iD8DBQE/1mynvJuQZxSWSsgRAk0HAKDKTHglcodYw2G9j5Il60e96Vv/xwCfcZ6p
ffIBwsqFtqW0UABYttqzT3U=
=JV2a
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-12-10 00:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-05 21:51 Fetch a single record David Shadovitz <david_shadovitz@xontech.com>
2003-12-09 20:21 ` Peter Eisentraut <peter_e@gmx.net>
2003-12-10 00:11 ` Paul Thomas <paul@tmsl.demon.co.uk>
2003-12-10 00:45 ` greg@turnstep.com
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