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 D98F453392 for ; Thu, 12 May 2005 13:12:59 -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 71813-07 for ; Thu, 12 May 2005 16:12:50 +0000 (GMT) Received: from mail.liquidcore.com (mail.liquidcore.net [68.17.96.60]) by svr1.postgresql.org (Postfix) with ESMTP id 9565E52A60 for ; Thu, 12 May 2005 13:12:49 -0300 (ADT) Received: (from root@localhost) by mail.liquidcore.com (8.13.1/8.12.10) id j4CGCxe5023671; Thu, 12 May 2005 11:12:59 -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 j4CGCwGQ023638; Thu, 12 May 2005 11:12:58 -0500 Message-ID: <42838144.8000008@nqadmin.com> Date: Thu, 12 May 2005 11:16:04 -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: Scott Marlowe Cc: pgsql-admin@postgresql.org Subject: Re: Query Plan - Index Scan & Seq Scan References: <428370AC.3070207@nqadmin.com> <1115911416.3868.265.camel@state.g2switchworks.com> <42837B95.2090908@nqadmin.com> <1115913086.22291.1.camel@state.g2switchworks.com> In-Reply-To: <1115913086.22291.1.camel@state.g2switchworks.com> 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/156 X-Sequence-Number: 17585 EXPLAIN ANALYZE (SELECT id FROM a,b WHERE a.id = b.id AND code >2 AND b.account_id = 16221); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=141372.58..141462.28 rows=1 width=8) (actual time=726.172..726.172 rows=0 loops=1) Merge Cond: ("outer".id = "inner".id) -> Index Scan using id_idx on b (cost=0.00..14415.96 rows=171 width=4) (actual time=726.168..726.168 rows=0 loops=1) Filter: (account_id = 16221) -> Sort (cost=141372.58..141375.27 rows=1076 width=12) (never executed) Sort Key: a.id -> Seq Scan on a (cost=0.00..141318.40 rows=1076 width=12) (never executed) Filter: (code > 2) Total runtime: 726.253 ms (9 rows) Thanks, -Prasanth. Scott Marlowe wrote: > On Thu, 2005-05-12 at 10:51, Prasanth wrote: > >>I agree with you. >> >>But I have the where conditions on the tables I was expecting the planner to >>user index scan but it went for seq scan. >> >>I did a little testing using what you said. >> >>Below are the results. >> >>SELECT a.id FROM a,b WHERE a.id = b.id AND a.code >2 AND b.account_id = 16221; >> >>Total runtime: 18194.936 ms >> >>Then I set the seqscan off and ran the same query. >> >>Total runtime: 27.554 ms > > > Good! This tells us two things, 1: Your database can use the indexes > (sometimes indexes can't be used for various reasons, which are quickly > disappearing by the way.) and 2: Your database is making the wrong > choice about when to use a seq scan versus an index. > > What does the explain analyze output from that query say about row > estimates versus actual rows returned? > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >