X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 377A79FAD5A for ; Fri, 15 Sep 2006 05:51:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 39166-10 for ; Fri, 15 Sep 2006 05:51:39 -0300 (ADT) X-Greylist: delayed 00:12:08.909468 by SQLgrey- Received: from obelix.askesis.nl (laudanum.demon.nl [82.161.125.16]) by postgresql.org (Postfix) with ESMTP id F32FC9FA605 for ; Fri, 15 Sep 2006 05:51:38 -0300 (ADT) Received: obelix 172.31.0.1 from 172.31.1.1 172.31.1.1 via HTTP with MS-WebStorage 6.0.6249 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 Received: from panoramix by obelix; 15 Sep 2006 10:39:27 +0200 X-Mailer: Evolution 2.6.3 Content-class: urn:content-classes:message Subject: Why the difference in plans ?? Date: Fri, 15 Sep 2006 10:39:27 +0200 Message-ID: <1158309566.30482.29.camel@panoramix> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Why the difference in plans ?? thread-index: AcbYonQx5d4Li3mMTgm+3NXQGasC8w== From: "Joost Kraaijeveld" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.136 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200609/199 X-Sequence-Number: 20885 Hi, I have two table: customers and salesorders. salesorders have a foreign key to the customer If I run this query: SELECT=20 salesOrders.objectid,=20 salesOrders.ordernumber,=20 salesOrders.orderdate,=20 customers.objectid,=20 customers.customernumber,=20 customers.lastname=20 FROM prototype.salesorders=20 INNER JOIN prototype.customers ON (=20 customers.objectid =3D salesorders.customer=20 )=20 where=20 lastname ilike 'Boonk'=20 order by ordernumber asc LIMIT 1 WITHOUT "LIMIT 1" this query plan is executed (EXPLAIN ANALYZE): Sort (cost=3D41811.90..41812.78 rows=3D353 width=3D103) (actual = time=3D623.855..623.867 rows=3D7 loops=3D1) Sort Key: salesorders.ordernumber -> Nested Loop (cost=3D2.15..41796.96 rows=3D353 width=3D103) = (actual time=3D0.166..623.793 rows=3D7 loops=3D1) -> Seq Scan on customers (cost=3D0.00..21429.44 rows=3D118 = width=3D55) (actual time=3D0.037..623.325 rows=3D5 loops=3D1) Filter: (lastname ~~* 'Boonk'::text) -> Bitmap Heap Scan on salesorders (cost=3D2.15..172.06 = rows=3D44 width=3D88) (actual time=3D0.075..0.079 rows=3D1 loops=3D5) Recheck Cond: ("outer".objectid =3D salesorders.customer) -> Bitmap Index Scan on orders_customer = (cost=3D0.00..2.15 rows=3D44 width=3D0) (actual time=3D0.066..0.066 = rows=3D1 loops=3D5) Index Cond: ("outer".objectid =3D = salesorders.customer) Total runtime: 624.051 ms With the limit this query plan is used (EXPLAIN ANALYZE): Limit (cost=3D0.00..18963.24 rows=3D1 width=3D103) (actual = time=3D18404.730..18404.732 rows=3D1 loops=3D1) -> Nested Loop (cost=3D0.00..6694025.41 rows=3D353 width=3D103) = (actual time=3D18404.723..18404.723 rows=3D1 loops=3D1) -> Index Scan using prototype_orders_ordernumber on salesorders = (cost=3D0.00..37263.14 rows=3D1104381 width=3D88) (actual = time=3D26.715..1862.408 rows=3D607645 loops=3D1) -> Index Scan using pk_prototype_customers on customers = (cost=3D0.00..6.02 rows=3D1 width=3D55) (actual time=3D0.023..0.023 = rows=3D0 loops=3D607645) Index Cond: (customers.objectid =3D "outer".customer) Filter: (lastname ~~* 'Boonk'::text) Total runtime: 18404.883 ms Both tables are freshly fully vacuumed analyzed. Why the difference and can I influence the result so that the first query plan (which is the fastest) is actually used in both cases (I would expect that the limit would be done after the sort?)?=20 TIA --=20 Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl