Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 2ADED475888 for ; Sat, 18 May 2002 18:26:19 -0400 (EDT) Received: from heidegger.catalyst.net.nz (203-96-145-72.adsl.paradise.net.nz [203.96.145.72]) by postgresql.org (Postfix) with ESMTP id 78F30475AC9 for ; Sat, 18 May 2002 18:25:06 -0400 (EDT) Received: from 127.0.0.1 (ident=unknown) by heidegger.catalyst.net.nz with esmtp (masqmail 0.2.8) id 179CcX-1JQ-00; Sun, 19 May 2002 10:24:13 +1200 Subject: Re: using CURSOR with PHP From: Andrew McMillan To: Keary Suska Cc: Postgres-PHP In-Reply-To: References: Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.5 Date: 19 May 2002 10:24:13 +1200 Message-Id: <1021760653.30045.296.camel@kant.mcmillan.net.nz> Mime-Version: 1.0 X-Archive-Number: 200205/65 X-Sequence-Number: 1197 On Sun, 2002-05-19 at 06:32, Keary Suska wrote: > > The drawback to using LIMIT is that you are executing the query on every > call, and Postgres cannot optimize on a LIMIT as was mentioned, because the > entire query has to be collected before the LIMIT can be applied. However, > IIRC, Postgres does query result caching, so subsequent calls on the same > query will tend to be faster, providing there is enough memory allocated to > support it. PostgreSQL will certainly take LIMIT into account when planning queries. EXPLAIN SELECT * FROM mybigtable WHERE x = y; and EXPLAIN SELECT * FROM mybigtable WHERE x = y LIMIT n; Will regularly show different query plans. For more complex queries, however, the plans are less likely to differ by much. OTOH PostgreSQL does _not_ do result caching, unless you have applied some patches that were floating around last year some time. Regards, Andrew. -- -------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Are you enrolled at http://schoolreunions.co.nz/ yet?