X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8E6EF9F9430 for ; Thu, 11 May 2006 09:58:14 -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 09855-04 for ; Thu, 11 May 2006 09:58:04 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.207]) by postgresql.org (Postfix) with ESMTP id 756E69FA176 for ; Thu, 11 May 2006 09:58:04 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id s13so141230wxc for ; Thu, 11 May 2006 05:58:03 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:user-agent:date:subject:from:to:message-id:thread-topic:thread-index:mime-version:content-type; b=Q4jndobNeJu/ppjQy50u8nLCWBkK9bZj1U0e5GFB91B6UlNW+letNoAwct+IWfqxgdam5Wd3mgEEXwDqxB4d8CvwiHId6NQK+5r8i3J42gBYT4QOsFVNjX0uIy4r+/2viAXtfsyKi++C76MZHMRuW/z2NgnkA740F+TJhhufQ2U= Received: by 10.70.51.3 with SMTP id y3mr662415wxy; Thu, 11 May 2006 05:58:03 -0700 (PDT) Received: from ?192.168.0.12? ( [24.227.61.54]) by mx.gmail.com with ESMTP id i34sm5888492wxd.2006.05.11.05.58.01; Thu, 11 May 2006 05:58:02 -0700 (PDT) User-Agent: Microsoft-Entourage/11.2.3.060209 Date: Thu, 11 May 2006 08:57:48 -0400 Subject: Nested Loops vs. Hash Joins or Merge Joins From: Ketema Harris To: "pgsql-performance@postgresql.org" Message-ID: Thread-Topic: Nested Loops vs. Hash Joins or Merge Joins Thread-Index: AcZ0+oDov6NU3uDtEdqGSgARJHlfcA== Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3230182682_18308268" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/290 X-Sequence-Number: 19077 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3230182682_18308268 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable I am attempting to learn more about the way Pg decides what operators to us= e in its query planning and executions. I have moderately complicated table layout, but it is mostly normalized I recently created a query: select account.acct_name as "Customer Name", NULL as "Facility", account.acct_number as "LDC Acct#", account.svc_address as "Service Address", account.svc_address as "Service Address", account.svc_city as "Service City", account.svc_state as "Service State", account.svc_city as "Service City", account.svc_zip as "Service Zip", product.ldc_name as "LDC", NULL as "ESI Rate", NULL as "LDC Rate", account.billing_address as "Mailing Address1", account.billing_address_2 as "Mailing Address2", account.billing_city || ', ' || account.billing_state as "City, State", account.billing_zip as "Zip", customer.first_name || ' ' || customer.last_name as "Contact", customer.phone as "Phone", customer.class as "Customer Class"= , NULL as "Tax Exempt", NULL as "Exempt%", marketer_divisions.channel_partner_code as "Channel Partner", NULL as "AE", NULL as "Annual Use MCF", account.rate as "Trigger Price", marketer_divisions.channel_partner_fee as "Channel Partner Fee" from naes.reconciliation inner join naes.application inner join naes.account inner join naes.marketer_product inner join naes.marketer_divisions inner join naes.cities on marketer_divisions.city_id =3D cities.city_id on marketer_product.division_id =3D marketer_divisions.division_i= d inner join naes.product on marketer_product.ldc_id =3D product.ldc_id on account.marketer_product_id =3D marketer_product.marketer_product_id inner join naes.customer on account.customer_id =3D customer.customer_id on account.app_id =3D application.app_id and account.acct_id =3D application.acct_id on reconciliation.app_id =3D application.app_id and reconciliation.transferred_date is NULL; The query runs fine I have no performance issues with it, but here are two query plans for the above query, one with nested loops on, the other with them off: Nested Loops on: Nested Loop (cost=3D3.33..11.37 rows=3D1 width=3D268) (actual time=3D2.166..2.982 rows=3D3 loops=3D1) Join Filter: ("outer".city_id =3D "inner".city_id) -> Nested Loop (cost=3D3.33..10.32 rows=3D1 width=3D272) (actual time=3D2.136..2.863 rows=3D3 loops=3D1) Join Filter: ("outer".division_id =3D "inner".division_id)plication.app_id and reco=3D -> Nested Loop (cost=3D3.33..9.27 rows=3D1 width=3D231) (actual time=3D2.119..2.763 rows=3D3 loops=3D1) Join Filter: ("outer".ldc_id =3D "inner".ldc_id) -> Nested Loop (cost=3D3.33..8.23 rows=3D1 width=3D218) (actual time=3D2.101..2.659 rows=3D3 loops=3D1) -> Nested Loop (cost=3D3.33..5.15 rows=3D1 width=3D151) (actual time=3D2.068..2.559 rows=3D3 loops=3D1) Join Filter: ("inner".app_id =3D "outer".app_id) -> Merge Join (cost=3D3.33..4.11 rows=3D1 width=3D159) (actual time=3D1.096..1.477 rows=3D31 loops=3D1) Merge Cond: ("outer".marketer_product_id =3D "inner".marketer_product_id) -> Index Scan using "PK_marketer_product_id" on marketer_product (cost=3D0.00..3.04 rows=3D4 width=3D12) (actual time=3D0.017..0.033 rows=3D4 loops=3D1) -> Sort (cost=3D3.33..3.33 rows=3D1 width=3D155) (actual time=3D1.065..1.180 rows=3D31 loops=3D1) Sort Key: account.marketer_product_i= d -> Hash Join (cost=3D1.75..3.32 rows=3D1 width=3D155) (actual time=3D0.457..0.848 rows=3D31 loops=3D1) Hash Cond: (("outer".app_id =3D "inner".app_id) AND ("outer".acct_id =3D "inner".acct_id)) -> Seq Scan on account (cost=3D0.00..1.28 rows=3D28 width=3D155) (actual time=3D0.007..0.160 rows=3D34 loops=3D1) -> Hash (cost=3D1.50..1.50 rows=3D50 width=3D8) (actual time=3D0.413..0.413 rows=3D50 loops=3D1) -> Seq Scan on application (cost=3D0.00..1.50 rows=3D50 width=3D8) (actual time=3D0.006..0.209 rows=3D50 loops=3D1) -> Seq Scan on reconciliation (cost=3D0.00..1.03 rows=3D1 width=3D4) (actual time=3D0.005..0.016 rows=3D3 loops=3D31) Filter: (transferred_date IS NULL) -> Index Scan using customer_pkey on customer (cost=3D0.00..3.06 rows=3D1 width=3D75) (actual time=3D0.011..0.015 rows=3D1 loops=3D3) Index Cond: ("outer".customer_id =3D customer.customer_id) -> Seq Scan on product (cost=3D0.00..1.02 rows=3D2 width=3D21) (actual time=3D0.005..0.013 rows=3D2 loops=3D3) -> Seq Scan on marketer_divisions (cost=3D0.00..1.02 rows=3D2 width=3D49) (actual time=3D0.005..0.013 rows=3D2 loops=3D3) -> Seq Scan on cities (cost=3D0.00..1.02 rows=3D2 width=3D4) (actual time=3D0.005..0.013 rows=3D2 loops=3D3) Total runtime: 3.288 ms Nested Loops off: Hash Join (cost=3D8.27..11.78 rows=3D1 width=3D268) (actual time=3D1.701..1.765 rows=3D3 loops=3D1) Hash Cond: ("outer".city_id =3D "inner".city_id) -> Hash Join (cost=3D7.24..10.73 rows=3D1 width=3D272) (actual time=3D1.629..1.667 rows=3D3 loops=3D1) Hash Cond: ("outer".customer_id =3D "inner".customer_id) -> Seq Scan on customer (cost=3D0.00..3.32 rows=3D32 width=3D75) (actual time=3D0.006..0.136 rows=3D33 loops=3D1) -> Hash (cost=3D7.24..7.24 rows=3D1 width=3D205) (actual time=3D1.366..1.366 rows=3D3 loops=3D1) -> Hash Join (cost=3D6.43..7.24 rows=3D1 width=3D205) (actual time=3D1.243..1.333 rows=3D3 loops=3D1) Hash Cond: ("outer".division_id =3D "inner".division_id) -> Hash Join (cost=3D5.40..6.20 rows=3D1 width=3D164) (actual time=3D1.184..1.252 rows=3D3 loops=3D1) Hash Cond: ("outer".ldc_id =3D "inner".ldc_id) -> Merge Join (cost=3D4.38..5.16 rows=3D1 width=3D151) (actual time=3D1.124..1.169 rows=3D3 loops=3D1) Merge Cond: ("outer".marketer_product_id =3D "inner".marketer_product_id) -> Index Scan using "PK_marketer_product_id" on marketer_product (cost=3D0.00..3.04 rows=3D4 width=3D12) (actual time=3D0.012..0.019 rows=3D2 loops=3D1) -> Sort (cost=3D4.38..4.38 rows=3D1 width=3D147) (actual time=3D1.098..1.109 rows=3D3 loops=3D1) Sort Key: account.marketer_product_i= d -> Hash Join (cost=3D2.78..4.37 rows=3D1 width=3D147) (actual time=3D1.007..1.064 rows=3D3 loops=3D1) Hash Cond: ("outer".app_id =3D "inner".app_id) -> Hash Join (cost=3D1.75..3.3= 2 rows=3D1 width=3D155) (actual time=3D0.494..0.875 rows=3D31 loops=3D1) Hash Cond: (("outer".app_id =3D "inner".app_id) AND ("outer".acct_id =3D "inner".acct_id)) -> Seq Scan on account (cost=3D0.00..1.28 rows=3D28 width=3D155) (actual time=3D0.007..0.154 rows=3D34 loops=3D1) -> Hash (cost=3D1.50..1.50 rows=3D50 width=3D8) (actual time=3D0.451..0.451 rows=3D50 loops=3D1= ) -> Seq Scan on application (cost=3D0.00..1.50 rows=3D50 width=3D8) (actual time=3D0.006..0.223 rows=3D50 loops=3D1) -> Hash (cost=3D1.03..1.03 rows=3D1 width=3D4) (actual time=3D0.042..0.042 rows=3D3 loops=3D1) -> Seq Scan on reconciliation (cost=3D0.00..1.03 rows=3D1 width=3D4) (actual time=3D0.007..0.019 rows=3D3 loops=3D1) Filter: (transferred_date IS NULL) -> Hash (cost=3D1.02..1.02 rows=3D2 width=3D21) (actual time=3D0.036..0.036 rows=3D2 loops=3D1) -> Seq Scan on product (cost=3D0.00..1.02 rows=3D2 width=3D21) (actual time=3D0.005..0.014 rows=3D2 loops=3D1) -> Hash (cost=3D1.02..1.02 rows=3D2 width=3D49) (actual time=3D0.036..0.036 rows=3D2 loops=3D1) -> Seq Scan on marketer_divisions (cost=3D0.00..1.02 rows=3D2 width=3D49) (actual time=3D0.007..0.016 rows=3D2 loops=3D1) -> Hash (cost=3D1.02..1.02 rows=3D2 width=3D4) (actual time=3D0.039..0.039 rows=3D2 loops=3D1) -> Seq Scan on cities (cost=3D0.00..1.02 rows=3D2 width=3D4) (actual time=3D0.009..0.017 rows=3D2 loops=3D1) Total runtime: 2.084 ms With nested loops enabled does it choose to use them because it sees the estimated start up cost with loops as less? Does it not know that the tota= l query would be faster with the Hash Joins? This query is in development right now, and as such there are not many rows. When it goes to production the reconciliation table will grow by about 50 =AD 100 rows per day where the transferred_date is NULL (this is the driving criteria behind this query.) As the table grows can I expect Pg to realize the the nested loops will be slower and will it switch to the Hash Joins? If not how would I force it t= o use the Hash Joins without just turning off nested loops completely? Is it a good idea to turn off nested loops completely? Statistics collecting and auto vacuum is enabled btw. I have an erd diagra= m showing the table structures if anyone is interested in looking at it, just let me know. Thanks, Ketema --B_3230182682_18308268 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Nested Loops vs. Hash Joins or Merge Joins I am attempting to lea= rn more about the way Pg decides what operators to use in its query planning= and executions.  I have moderately complicated table layout, but it is= mostly normalized I recently created a query:

select account.acct_name as "Customer Name", NULL as "Facili= ty", account.acct_number as "LDC Acct#", account.svc_address = as "Service Address",
account.svc_address as "Service Address", account.svc_city as &qu= ot;Service City", account.svc_state as "Service State", accou= nt.svc_city as "Service City",
account.svc_zip as "Service Zip", product.ldc_name as "LDC&q= uot;, NULL as "ESI Rate", NULL as "LDC Rate",
account.billing_address as "Mailing Address1", account.billing_ad= dress_2 as "Mailing Address2",
account.billing_city || ', ' || account.billing_state as "City, State&= quot;, account.billing_zip as "Zip", customer.first_name || ' ' ||= customer.last_name
as "Contact", customer.phone as "Phone", customer.class= as "Customer Class", NULL as "Tax Exempt", NULL as &quo= t;Exempt%",
marketer_divisions.channel_partner_code as "Channel Partner", NUL= L as "AE", NULL as "Annual Use MCF", account.rate as &qu= ot;Trigger Price",
marketer_divisions.channel_partner_fee as "Channel Partner Fee" from naes.reconciliation
   inner join naes.application
      inner join naes.account
         inner join naes.marke= ter_product
            inn= er join naes.marketer_divisions
            &nb= sp;  inner join naes.cities
            &nb= sp;  on marketer_divisions.city_id =3D cities.city_id
            on = marketer_product.division_id =3D marketer_divisions.division_id
            inn= er join naes.product
            on = marketer_product.ldc_id =3D product.ldc_id
         on account.marketer_p= roduct_id =3D marketer_product.marketer_product_id
         inner join naes.custo= mer
         on account.customer_i= d =3D customer.customer_id
      on account.app_id =3D application.app_id = and account.acct_id =3D application.acct_id
   on reconciliation.app_id =3D application.app_id and reconci= liation.transferred_date is NULL;

The query runs fine I have no performance issues with it, but here are two = query plans for the above query, one with nested loops on, the other with th= em off:

Nested Loops on:

Nested Loop  (cost=3D3.33..11.37 rows=3D1 width=3D268) (actual time=3D2.166..2= .982 rows=3D3 loops=3D1)
   Join Filter: ("outer".city_id =3D "inner&quo= t;.city_id)
   ->  Nested Loop  (cost=3D3.33..10.32 rows=3D1 wi= dth=3D272) (actual time=3D2.136..2.863 rows=3D3 loops=3D1)
         Join Filter: ("o= uter".division_id =3D "inner".division_id)plication.app_id and = reco=3D
         ->  Nested Lo= op  (cost=3D3.33..9.27 rows=3D1 width=3D231) (actual time=3D2.119..2.763 rows=3D3= loops=3D1)
            &nb= sp;  Join Filter: ("outer".ldc_id =3D "inner".ld= c_id)
            &nb= sp;  ->  Nested Loop  (cost=3D3.33..8.23 rows=3D1 width=3D2= 18) (actual time=3D2.101..2.659 rows=3D3 loops=3D1)
            &nb= sp;        ->  Nested Loop &= nbsp;(cost=3D3.33..5.15 rows=3D1 width=3D151) (actual time=3D2.068..2.559 rows=3D3 loo= ps=3D1)
            &nb= sp;            &= nbsp; Join Filter: ("inner".app_id =3D "outer".app_id= )
            &nb= sp;            &= nbsp; ->  Merge Join  (cost=3D3.33..4.11 rows=3D1 width=3D159) (= actual time=3D1.096..1.477 rows=3D31 loops=3D1)
            &nb= sp;            &= nbsp;       Merge Cond: ("outer"= ;.marketer_product_id =3D "inner".marketer_product_id)
            &nb= sp;            &= nbsp;       ->  Index Scan using = "PK_marketer_product_id" on marketer_product  (cost=3D0.00..3.0= 4 rows=3D4 width=3D12) (actual time=3D0.017..0.033 rows=3D4 loops=3D1)
            &nb= sp;            &= nbsp;       ->  Sort  (cost=3D= 3.33..3.33 rows=3D1 width=3D155) (actual time=3D1.065..1.180 rows=3D31 loops=3D1)
            &nb= sp;            &= nbsp;            = ; Sort Key: account.marketer_product_id
            &nb= sp;            &= nbsp;            = ; ->  Hash Join  (cost=3D1.75..3.32 rows=3D1 width=3D155) (actua= l time=3D0.457..0.848 rows=3D31 loops=3D1)
            &nb= sp;            &= nbsp;            = ;       Hash Cond: (("outer".ap= p_id =3D "inner".app_id) AND ("outer".acct_id =3D "inne= r".acct_id))
            &nb= sp;            &= nbsp;            = ;       ->  Seq Scan on account &= nbsp;(cost=3D0.00..1.28 rows=3D28 width=3D155) (actual time=3D0.007..0.160 rows=3D34 l= oops=3D1)
            &nb= sp;            &= nbsp;            = ;       ->  Hash  (cost=3D1.50= ..1.50 rows=3D50 width=3D8) (actual time=3D0.413..0.413 rows=3D50 loops=3D1)
            &nb= sp;            &= nbsp;            = ;            &nb= sp;->  Seq Scan on application  (cost=3D0.00..1.50 rows=3D50 width=3D= 8) (actual time=3D0.006..0.209 rows=3D50 loops=3D1)
            &nb= sp;            &= nbsp; ->  Seq Scan on reconciliation  (cost=3D0.00..1.03 row= s=3D1 width=3D4) (actual time=3D0.005..0.016 rows=3D3 loops=3D31)
            &nb= sp;            &= nbsp;       Filter: (transferred_date IS = NULL)
            &nb= sp;        ->  Index Scan us= ing customer_pkey on customer  (cost=3D0.00..3.06 rows=3D1 width=3D75) (actua= l time=3D0.011..0.015 rows=3D1 loops=3D3)
            &nb= sp;            &= nbsp; Index Cond: ("outer".customer_id =3D customer.customer_id= )
            &nb= sp;  ->  Seq Scan on product  (cost=3D0.00..1.02 rows=3D2= width=3D21) (actual time=3D0.005..0.013 rows=3D2 loops=3D3)
         ->  Seq Scan = on marketer_divisions  (cost=3D0.00..1.02 rows=3D2 width=3D49) (actual time=3D0= .005..0.013 rows=3D2 loops=3D3)
   ->  Seq Scan on cities  (cost=3D0.00..1.02 row= s=3D2 width=3D4) (actual time=3D0.005..0.013 rows=3D2 loops=3D3)
 Total runtime: 3.288 ms

Nested Loops off:

Hash Join  (cost=3D8.27..11.78 rows=3D1 width=3D268) (actual time=3D1.701..1.7= 65 rows=3D3 loops=3D1)
   Hash Cond: ("outer".city_id =3D "inner"= .city_id)
   ->  Hash Join  (cost=3D7.24..10.73 rows=3D1 widt= h=3D272) (actual time=3D1.629..1.667 rows=3D3 loops=3D1)
         Hash Cond: ("out= er".customer_id =3D "inner".customer_id)
         ->  Seq Scan = on customer  (cost=3D0.00..3.32 rows=3D32 width=3D75) (actual time=3D0.006..0.1= 36 rows=3D33 loops=3D1)
         ->  Hash &nbs= p;(cost=3D7.24..7.24 rows=3D1 width=3D205) (actual time=3D1.366..1.366 rows=3D3 loops=3D= 1)
            &nb= sp;  ->  Hash Join  (cost=3D6.43..7.24 rows=3D1 width=3D205= ) (actual time=3D1.243..1.333 rows=3D3 loops=3D1)
            &nb= sp;        Hash Cond: ("outer&q= uot;.division_id =3D "inner".division_id)
            &nb= sp;        ->  Hash Join &nb= sp;(cost=3D5.40..6.20 rows=3D1 width=3D164) (actual time=3D1.184..1.252 rows=3D3 loops= =3D1)
            &nb= sp;            &= nbsp; Hash Cond: ("outer".ldc_id =3D "inner".ldc_id)<= BR>             &nb= sp;            &= nbsp; ->  Merge Join  (cost=3D4.38..5.16 rows=3D1 width=3D151) (= actual time=3D1.124..1.169 rows=3D3 loops=3D1)
            &nb= sp;            &= nbsp;       Merge Cond: ("outer"= ;.marketer_product_id =3D "inner".marketer_product_id)
            &nb= sp;            &= nbsp;       ->  Index Scan using = "PK_marketer_product_id" on marketer_product  (cost=3D0.00..3.0= 4 rows=3D4 width=3D12) (actual time=3D0.012..0.019 rows=3D2 loops=3D1)
            &nb= sp;            &= nbsp;       ->  Sort  (cost=3D= 4.38..4.38 rows=3D1 width=3D147) (actual time=3D1.098..1.109 rows=3D3 loops=3D1)
            &nb= sp;            &= nbsp;            = ; Sort Key: account.marketer_product_id
            &nb= sp;            &= nbsp;            = ; ->  Hash Join  (cost=3D2.78..4.37 rows=3D1 width=3D147) (actua= l time=3D1.007..1.064 rows=3D3 loops=3D1)
            &nb= sp;            &= nbsp;            = ;       Hash Cond: ("outer".app= _id =3D "inner".app_id)
            &nb= sp;            &= nbsp;            = ;       ->  Hash Join  (cost= =3D1.75..3.32 rows=3D1 width=3D155) (actual time=3D0.494..0.875 rows=3D31 loops=3D1)
            &nb= sp;            &= nbsp;            = ;            &nb= sp;Hash Cond: (("outer".app_id =3D "inner".app_id) AND (&q= uot;outer".acct_id =3D "inner".acct_id))
            &nb= sp;            &= nbsp;            = ;            &nb= sp;->  Seq Scan on account  (cost=3D0.00..1.28 rows=3D28 width=3D155)= (actual time=3D0.007..0.154 rows=3D34 loops=3D1)
            &nb= sp;            &= nbsp;            = ;            &nb= sp;->  Hash  (cost=3D1.50..1.50 rows=3D50 width=3D8) (actual time=3D0.4= 51..0.451 rows=3D50 loops=3D1)
            &nb= sp;            &= nbsp;            = ;            &nb= sp;      ->  Seq Scan on application &= nbsp;(cost=3D0.00..1.50 rows=3D50 width=3D8) (actual time=3D0.006..0.223 rows=3D50 loo= ps=3D1)
            &nb= sp;            &= nbsp;            = ;       ->  Hash  (cost=3D1.03= ..1.03 rows=3D1 width=3D4) (actual time=3D0.042..0.042 rows=3D3 loops=3D1)
            &nb= sp;            &= nbsp;            = ;            &nb= sp;->  Seq Scan on reconciliation  (cost=3D0.00..1.03 rows=3D1 widt= h=3D4) (actual time=3D0.007..0.019 rows=3D3 loops=3D1)
            &nb= sp;            &= nbsp;            = ;            &nb= sp;      Filter: (transferred_date IS NULL)             &nb= sp;            &= nbsp; ->  Hash  (cost=3D1.02..1.02 rows=3D2 width=3D21) (actual = time=3D0.036..0.036 rows=3D2 loops=3D1)
            &nb= sp;            &= nbsp;       ->  Seq Scan on produ= ct  (cost=3D0.00..1.02 rows=3D2 width=3D21) (actual time=3D0.005..0.014 rows=3D2 = loops=3D1)
            &nb= sp;        ->  Hash  (c= ost=3D1.02..1.02 rows=3D2 width=3D49) (actual time=3D0.036..0.036 rows=3D2 loops=3D1)             &nb= sp;            &= nbsp; ->  Seq Scan on marketer_divisions  (cost=3D0.00..1.02= rows=3D2 width=3D49) (actual time=3D0.007..0.016 rows=3D2 loops=3D1)
   ->  Hash  (cost=3D1.02..1.02 rows=3D2 width=3D4) (= actual time=3D0.039..0.039 rows=3D2 loops=3D1)
         ->  Seq Scan = on cities  (cost=3D0.00..1.02 rows=3D2 width=3D4) (actual time=3D0.009..0.017 r= ows=3D2 loops=3D1)
 Total runtime: 2.084 ms

With nested loops enabled does it choose to use them because it sees the es= timated start up cost with loops as less?  Does it not know that the to= tal query would be faster with the Hash Joins?  This query is in develo= pment right now, and as such there are not many rows.  When it goes to = production the reconciliation table will grow by about 50 – 100 rows p= er day where the transferred_date is NULL (this is the driving criteria behi= nd this query.)  As the table grows can I expect Pg to realize the the = nested loops will be slower and will it switch to the Hash Joins?  If n= ot how would I force it to use the Hash Joins without just turning off neste= d loops completely?  Is it a good idea to turn off nested loops complet= ely?

Statistics collecting and auto vacuum is enabled btw.  I have an erd d= iagram showing the table structures if anyone is interested in looking at it= , just let me know.

Thanks,
Ketema
 --B_3230182682_18308268--