X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9C84E4762FE for ; Wed, 23 Apr 2003 12:21:39 -0400 (EDT) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id 6BFE3475FC8 for ; Wed, 23 Apr 2003 12:21:29 -0400 (EDT) Received: (qmail 18129 invoked from network); 23 Apr 2003 16:21:26 -0000 Received: from unknown (HELO www.trade-india.com) (unknown) by unknown with SMTP; 23 Apr 2003 16:21:26 -0000 Received: (qmail 26797 invoked by uid 99); 23 Apr 2003 16:21:21 -0000 Received: from 219.65.235.51 (SquirrelMail authenticated user mallah) by mail.trade-india.com with HTTP; Wed, 23 Apr 2003 21:51:21 +0530 (IST) Message-ID: <1220.219.65.235.51.1051114881.squirrel@mail.trade-india.com> Date: Wed, 23 Apr 2003 21:51:21 +0530 (IST) Subject: Why is seq search preferred here by planner? From: To: X-Priority: 3 Importance: Normal X-MSMail-Priority: Normal X-Mailer: SquirrelMail (version 1.2.6) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-4.7 required=5.0 tests=BAYES_10,MISSING_MIMEOLE,MISSING_OUTLOOK_NAME,NO_REAL_NAME version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/325 X-Sequence-Number: 13026 Hi Folks, I want to update the country feild in table email_bank by looking up another table personal_account_details by matching userid. any anyone tellme why an seq scan on personal_account_details for getting the coutry. the query is: UPDATE email_bank set country=3Dpersonal_account_details.country where user= id > 0 and userid=3Dpersonal_account_details.userid ; both tables have been vacuum analyzed before running the queries. my question is is the the best plan ? becoz i most get similar plan when i update in this manner. Its quite a decent IBM xseries server with 2GB ram and 4 Intel(R) XEON(TM) CPU 2.00GHz. and explain analyze is below: tradein_clients=3D# begin work;explain analyze UPDATE email_bank set country=3Dpersonal_account_details.country where userid > 0 and userid=3Dpersonal_account_details.userid ;BEGIN Time: 669.57 ms QUERY PLAN ---------------------------------------------------------------------------= ------------------------------------------------------------------- Hash Jo= in (cost=3D14496.79..36722.98 rows=3D216658 width=3D137) (actual time=3D35= 05.31..49707.67 rows=3D150756 loops=3D1) Hash Cond: ("outer".userid =3D "inner".userid) -> Seq Scan on email_bank (cost=3D0.00..16268.10 rows=3D216658 width= =3D123) (actual time=3D0.06..43033.91 rows=3D155714 loops=3D1) Filter: (userid >= 0) -> Hash (cost=3D14113.23..14113.23 rows=3D153423 width=3D14) (actual t= ime=3D3505.08..3505.08 rows=3D0 loops=3D1) -> Seq Scan on personal_account_details (cost=3D0.0= 0..14113.23 rows=3D153423 width=3D14) (actual time=3D0.04..3196.12 rows=3D153431 loops=3D1) Total runtim= e: 209472.14 msec (7 rows) Regds Mallah. ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/