X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 226D48B9E07 for ; Mon, 7 Feb 2005 11:54:10 +0000 (GMT) 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 45413-09 for ; Mon, 7 Feb 2005 11:53:58 +0000 (GMT) Received: from termit.gingerall.cz (termit.gingerall.cz [213.195.208.132]) by svr1.postgresql.org (Postfix) with ESMTP id 2BC8A8B9DFA for ; Mon, 7 Feb 2005 11:53:57 +0000 (GMT) Received: from [213.195.208.156] (balvan.gingerall.cz [213.195.208.156]) (authenticated bits=0) by termit.gingerall.cz (8.12.11/8.12.11) with ESMTP id j17Bro1Z007781 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Mon, 7 Feb 2005 12:53:50 +0100 Message-ID: <420756BA.4080507@gingerall.cz> Date: Mon, 07 Feb 2005 12:53:30 +0100 From: Jan Poslusny User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.5) Gecko/20041217 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Antony Paul Cc: pgsql-performance@postgresql.org Subject: Re: Index not used with or condition References: <2989532e05020701073638ca4c@mail.gmail.com> <2989532e0502070314402057cc@mail.gmail.com> In-Reply-To: <2989532e0502070314402057cc@mail.gmail.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200502/110 X-Sequence-Number: 10439 It depends on many circumstances, but, at first, simple question: Did you run vacuum analyze? I am satisfied with functional indexes - it works in my pg 7.4.x. Antony Paul wrote: >On more investigation I found that index scan is not used if the query >have a function in it like lower() and an index exist for lower() >column. > >rgds >Antony Paul > > >On Mon, 7 Feb 2005 14:37:15 +0530, Antony Paul wrote: > > >>Hi all, >> I am facing a strange problem when I run EXPLAIN against a table >>having more than 100000 records. The query have lot of OR conditions >>and when parts of the query is removed it is using index. To analyse >>it I created a table with a single column, inserted 100000 >>records(random number) in it created index and run a query which >>returns 1 record which have no or condition and it was using index. I >>added an OR conditon and is using sequential scan. I set the >>enable_seqscan to off. I ran the tests again and is using index scan. >> So which one I have to use. Is this any bug in Explain. >> >>rgds >>Antony Paul. >> >> >> > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > >