Received: from mailsys01.intnet.net (antares.intnet.net [198.252.32.143]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f59GNUE85599 for ; Sat, 9 Jun 2001 12:23:30 -0400 (EDT) (envelope-from jconway2@home.com) Received: from [24.0.42.111] (HELO jecw2k1) by mailsys01.intnet.net (CommuniGate Pro SMTP 3.3.2) with SMTP id 9468665; Sat, 09 Jun 2001 12:22:32 -0400 Message-ID: <005701c0f100$82acca70$0205a8c0@jecw2k1> From: "Joe Conway" To: "Subra Radhakrishnan" , References: <20010609151802.49099.qmail@web13806.mail.yahoo.com> Subject: Re: Inconsistent usage of Index Date: Sat, 9 Jun 2001 09:23:25 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200106/142 X-Sequence-Number: 3363 > Instance #2 > ----------- > explain select * from allied_medical_req_main where v_ttyp_code = 'XRAY'; > NOTICE: QUERY PLAN: > > Seq Scan on allied_medical_req_main (cost=0.00..79.49 rows=713 width=192) > > EXPLAIN > As you can see from Instance #1 and #2 above, the usage of Index is not consistent. Do you > have any suggestions? > > Thanks, > > Subra > > P.S: I also did vacuum on the database. However, I am not clear as to what actually it does. Among other things, "vacuum analyze" calculates statistics for the table columns, which are used by the optimizer to decide how to most efficiently execute the query. See http://www.postgresql.org/idocs/index.php?sql-vacuum.html for an explanation. See http://www.postgresql.org/idocs/index.php?performance-tips.html#USING-EXPLAI N for information on how to interpret the explain output. The output above on instance 2 shows 713 rows expected output. If this is a significant percent of the total number of rows in this table, then it *is* more efficient to perform a table scan instead of using the index. What is the total number or rows in this table? If you still really want to force an index scan, try issuing "SET ENABLE_SEQSCAN = OFF;". See http://www.postgresql.org/idocs/index.php?runtime-config.html#RUNTIME-CONFIG -OPTIMIZER for an explanation. Hope this helps, -- Joe