Re: [PERFORM] 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 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 <[EMAIL PROTECTED]> wrote: > > > > > >>Hi all, > >>I am facing a strange problem when I run EXPLAIN against a table > >>having more than 10 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 10 > >>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 [EMAIL PROTECTED] so that your > > message can get through to the mailing list cleanly > > > > > > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Index not used with or condition
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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Index not used with or condition
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 <[EMAIL PROTECTED]> wrote: Hi all, I am facing a strange problem when I run EXPLAIN against a table having more than 10 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 10 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] 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 [EMAIL PROTECTED])
Re: [PERFORM] 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 <[EMAIL PROTECTED]> wrote: > Hi all, > I am facing a strange problem when I run EXPLAIN against a table > having more than 10 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 10 > 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Index not used with or condition
Hi all, I am facing a strange problem when I run EXPLAIN against a table having more than 10 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 10 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
