Thread: Index not used with or condition
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.
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 <antonypaul24@gmail.com> 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. >
On Mon, Feb 07, 2005 at 04:44:07PM +0530, 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. What version are you using? 8.0 had fixes for this situation. /* Steinar */ -- Homepage: http://www.sesse.net/
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 <antonypaul24@gmail.com> 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 > > >
Sorry I forgot to mention it. I am using 7.3.3. I will try it in 8.0.0 rgds Antony Paul On Mon, 7 Feb 2005 12:46:05 +0100, Steinar H. Gunderson <sgunderson@bigfoot.com> wrote: > On Mon, Feb 07, 2005 at 04:44:07PM +0530, 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. > > What version are you using? 8.0 had fixes for this situation. > > /* Steinar */ > -- > Homepage: http://www.sesse.net/ > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
I ran analyze; several times. rgds Antony Paul On Mon, 07 Feb 2005 12:53:30 +0100, Jan Poslusny <pajout@gingerall.cz> wrote: > 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 <antonypaul24@gmail.com> 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 > > > > > > >