Thread: 7.1.2 query now uses hash-join instead of nested loop

Hi,

    I have two machines one running 7.1.1 and the other 7.1.2.  I've used
pg_dump to dump the schema and data from the 7.1.1 system and loaded it into
7.1.2 for testing.  Some of my queries now run a lot slower as the planner
prefers to use a hash-join instead of a nested loop.  I have run vacuum but
it hasn't altered the way the planners choice.  Please find below a copy of
the SQL used, the table definition and the output of the explain command
form the two versions of pg.

Best Regards,

Tim Knowles


SQL

 SELECT A.itemnumber, B.ourpartnumber, B.mfrpartnumber,
 B.custpartnumber,B.description, B.unit, A.quantity, A.itemheaderid,
 B.commoditycode

 FROM enq_tbl_enquiryitems A, gen_tbl_products B

 WHERE B.ourpartnumber=A.ourpartnumber AND A.projectnumber='1025' AND
 A.itemnumber='003';



TABLE DEFINITION


                  Table "gen_tbl_products"
        Attribute       |         Type          | Modifier
  ----------------------+-----------------------+----------
   ourpartnumber        | character varying(50) |
   mfrpartnumber        | character varying(50) |
   custpartnumber       | character varying(50) |
   otherpartnumber      | character varying(50) |
   description          | text                  |
   unit                 | character varying(50) |
   price                | double precision      |
   weightkg             | double precision      |
   origin               | bigint                |
   quantitybreak        | smallint              |
   discontinued         | smallint              |
   quantity             | bigint                |
   commoditycode        | character varying(50) |
   standardavailability | bigint                |
   currencycode         | character varying(6)  |
   volumem3             | double precision      |
  Index: gen_tbl_products_idx     #indexed on ourpartnumber

             Table "enq_tbl_enquiryitems"
     Attribute   |         Type          | Modifier
  ---------------+-----------------------+----------
   projectnumber | character varying(50) |
   ourpartnumber | character varying(50) |
   itemnumber    | character varying(50) |
   quantity      | bigint                |
   itemheaderid  | bigint                |
  Indices: enq_tbl_enquiryitems_idx,       #indexed on projectnumber &
itemnumber
           enq_tbl_enquiryitems_projectnum #indexed on projectnumber






EXPLAIN OUTPUT  7.1.2

  NOTICE:  QUERY PLAN:

  Hash Join  (cost=21.65..8878.45 rows=10037 width=112)
    ->  Seq Scan on gen_tbl_products b  (cost=0.00..2343.09 rows=100209
width=72)
    ->  Hash  (cost=21.63..21.63 rows=10 width=40)
          ->  Index Scan using enq_tbl_enquiryitems_idx on
enq_tbl_enquiryitems a  (cost=0.00..21.63 rows=10 width=40)



EXPLAIN OUTPUT  7.1.1

  NOTICE:  QUERY PLAN:

  Nested Loop  (cost=0.00..6.60 rows=1 width=112)
    ->  Index Scan using enq_tbl_enquiryitems_idx on
enq_tbl_enquiryitems a  (cost=0.00..3.06 rows=1 width=40)
    ->  Index Scan using gen_tbl_products_idx on gen_tbl_products b
(cost=0.00..3.52 rows=1 width=72)



Disclaimer:
1. This email is strictly confidential to the person to whom it has been sent.  If you believe you have received this
emailin error please contact Ametco International Limited on (020) 8963 1888 or email postmaster@ametco.co.uk 
2. Any views expressed in this email are the views of the author, not of Ametco International Ltd..

"Tim Knowles" <timknowles@ametco.co.uk> writes:
>     I have two machines one running 7.1.1 and the other 7.1.2.  I've used
> pg_dump to dump the schema and data from the 7.1.1 system and loaded it into
> 7.1.2 for testing.  Some of my queries now run a lot slower as the planner
> prefers to use a hash-join instead of a nested loop.

AFAICS from the CVS change logs, there is no difference in planning
between 7.1.1 and 7.1.2.  Perhaps you neglected to run VACUUM ANALYZE
on one case or the other?  The difference in estimated result rows seems
awfully large...

            regards, tom lane