Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 39B844758A2 for ; Sun, 19 May 2002 01:40:04 -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 5551B474E4E for ; Sun, 19 May 2002 01:40:02 -0400 (EDT) Received: from 127.0.0.1 (ident=unknown) by heidegger.catalyst.net.nz with esmtp (masqmail 0.2.8) id 179JPg-4Gv-00; Sun, 19 May 2002 17:39:24 +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 17:39:24 +1200 Message-Id: <1021786764.12773.66.camel@kant.mcmillan.net.nz> Mime-Version: 1.0 X-Archive-Number: 200205/69 X-Sequence-Number: 1201 On Sun, 2002-05-19 at 13:07, Keary Suska wrote: > > > For more complex queries, however, the plans are less likely to differ > > > Actually, as you can see with my examples above, the reverse is true, as the > simple query produced the same plan. The more complex the query, the more > choices the optimizer has for picking the best case, which unfortunately is > not always the truly best case. No, I meant _complex_ plans: pcno=# explain select * from vwmbradr where centre_id = 56; NOTICE: QUERY PLAN: Subquery Scan vwmbradr (cost=636.68..636.68 rows=1 width=418) -> Sort (cost=636.68..636.68 rows=1 width=418) -> Nested Loop (cost=0.00..636.67 rows=1 width=418) -> Nested Loop (cost=0.00..630.67 rows=1 width=340) -> Nested Loop (cost=0.00..624.68 rows=1 width=254) -> Index Scan using primary_centre_id_index on constituents (cost=0.00..622.35 rows=1 width=212) -> Seq Scan on centres (cost=0.00..1.59 rows=59 width=42) -> Index Scan using constituents_pkey on constituents (cost=0.00..5.98 rows=1 width=86) -> Index Scan using constituents_pkey on constituents (cost=0.00..5.98 rows=1 width=78) EXPLAIN pcno=# explain select * from vwmbradr where centre_id = 56 limit 1; NOTICE: QUERY PLAN: Limit (cost=636.68..636.68 rows=1 width=418) -> Subquery Scan vwmbradr (cost=636.68..636.68 rows=1 width=418) -> Sort (cost=636.68..636.68 rows=1 width=418) -> Nested Loop (cost=0.00..636.67 rows=1 width=418) -> Nested Loop (cost=0.00..630.67 rows=1 width=340) -> Nested Loop (cost=0.00..624.68 rows=1 width=254) -> Index Scan using primary_centre_id_index on constituents (cost=0.00..622.35 rows=1 width=212) -> Seq Scan on centres (cost=0.00..1.59 rows=59 width=42) -> Index Scan using constituents_pkey on constituents (cost=0.00..5.98 rows=1 width=86) -> Index Scan using constituents_pkey on constituents (cost=0.00..5.98 rows=1 width=78) EXPLAIN :-) Cheers, 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?