SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-18-2008, 11:05 AM
| ||||
| ||||
 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. ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org      |
|
04-18-2008, 11:05 AM
| |||
| |||
| Re: Index not used with or condition 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 |
|
04-18-2008, 11:05 AM
| |||
| |||
| Re: Index not used with or condition 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 > > > > > > > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster |
|
04-18-2008, 11:05 AM
| ||||
| ||||
| Re: Index not used with or condition 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) |
Linear Mode
