7.1.2 query now uses hash-join instead of nested loop - Mailing list pgsql-general
From | Tim Knowles |
---|---|
Subject | 7.1.2 query now uses hash-join instead of nested loop |
Date | |
Msg-id | NCBBKEPECLCBHBLKEPOIKEFPCGAA.timknowles@ametco.co.uk Whole thread Raw |
Responses |
Re: 7.1.2 query now uses hash-join instead of nested loop
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-general |
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..
pgsql-general by date: