X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0F598533BB for ; Thu, 12 May 2005 12:41:20 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 65671-08 for ; Thu, 12 May 2005 15:41:17 +0000 (GMT) Received: from mail.liquidcore.com (mail.liquidcore.net [68.17.96.60]) by svr1.postgresql.org (Postfix) with ESMTP id D184853236 for ; Thu, 12 May 2005 12:41:16 -0300 (ADT) Received: (from root@localhost) by mail.liquidcore.com (8.13.1/8.12.10) id j4CFfRnY020764; Thu, 12 May 2005 10:41:27 -0500 Received: from [192.168.0.81] (ppasalalinux.nqadmin.com [192.168.0.81]) by mail.liquidcore.com (8.13.1/8.12.10) with ESMTP id j4CFfRd4020732; Thu, 12 May 2005 10:41:27 -0500 Message-ID: <428379E0.4010709@nqadmin.com> Date: Thu, 12 May 2005 10:44:32 -0500 From: Prasanth Reply-To: dbadmin@nqadmin.com User-Agent: Mozilla Thunderbird 1.0.2-1.3.2 (X11/20050324) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: Query Plan - Index Scan & Seq Scan References: <428370AC.3070207@nqadmin.com> <886.1115910870@sss.pgh.pa.us> In-Reply-To: <886.1115910870@sss.pgh.pa.us> X-Enigmail-Version: 0.90.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-scanner: scanned for malicious content X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.051 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200505/152 X-Sequence-Number: 17581 Thanks for the prompt reply. Table a has about 6 million and table b had a little more than half a million. Sorry I wasn't exact about my numbers before. I will be having the where conditions on both the tables that would bring down the count drastically. Even in this case the planner is going for a seq scan. A where condition I always use is shown below. This is bringing down the number of rows from 6.5m to 1210. I have an index on code also. Even here it is going for seq scan. EXPLAIN ANALYZE SELECT count(*) fROM a where Code >2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=141321.09..141321.09 rows=1 width=0) (actual time=6454.063..6454.064 rows=1 loops=1) -> Seq Scan on a (cost=0.00..141318.40 rows=1076 width=0) (actual time=15.687..6453.292 rows=1210 loops=1) Filter: (code > 2) Total runtime: 6454.140 ms (4 rows) Below is example where I have where conditions on both the tables this in effect is limiting the number of rows from each tables a & b to 171 & 1076 respectively. EXPLAIN (SELECT fund_value FROM b INNER JOIN a ON a.id = b.id WHERE code >2 AND b.account_id = 16221); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=141372.58..141462.28 rows=1 width=8) Merge Cond: ("outer".id = "inner".id) -> Index Scan using id_idx on b (cost=0.00..14415.96 rows=171 width=4) Filter: (account_id = 16221) -> Sort (cost=141372.58..141375.27 rows=1076 width=12) Sort Key: a.id -> Seq Scan on a (cost=0.00..141318.40 rows=1076 width=12) Filter: (code > 2) (8 rows) Thanks, -Prasanth. Tom Lane wrote: > Prasanth writes: > >> Hash Join (cost=13865.30..326413.23 rows=6451 width=18) >> Hash Cond: ("outer".id = "inner".id) >> -> Seq Scan on a (cost=0.00..125076.37 rows=6450937 width=18) >> -> Hash (cost=10168.64..10168.64 rows=500664 width=4) >> -> Seq Scan on b (cost=0.00..10168.64 rows=500664 width=4) >>(5 rows) > > >>The planner used to perform a index scan. I have added a lot of data in those >>two tables. Right now both tables have millions of records. > > > It is highly unlikely that you want an index scan for joining millions > of records ... > > However, if you do have millions in both tables, why does the planner > think there are only 500664 rows in b? Maybe you are overdue for ANALYZE. > > regards, tom lane > >