Обсуждение: 7.2b2 problem using like 'XXX%' sequential scan
Ciao, I'm testing 7.2b2 and I see it is much slower when I use like on column with indexes, the explain sais it is a sequantial scan (same as 7.1.3), but the time it get to complete is much longer. I got the problem only on SMP machine: explain on 7.1.3 say it is a seq scan with cost 18837 explain on 7.2b2 say it is a seq scan 22.5 on single cpu 7.2b2 says: Index scan with cost 29 and it is very fast!! All platform are linux/i386 using redhat 7.2 and latest libraries I'm testing latest snapshot just now. thanks -- ------------------------------------------------------- Giuseppe Tanzilli g.tanzilli@gruppocsf.com CSF Sistemi srl phone ++39 0775 7771 Via del Ciavattino Anagni FR Italy
Yes, I vacuumed/reindexed the db, just tested the latest snapshot, same problem. bye Antonio Fiol Bonnín wrote: >Did you VACUUM ANALYZE on your SMP machine? > >Antonio > >Giuseppe Tanzilli - CSF wrote: > >>Ciao, >>I'm testing 7.2b2 and I see it is much slower when I use like on column >>with indexes, >>the explain sais it is a sequantial scan (same as 7.1.3), but the time >>it get to complete is much longer. >> >>I got the problem only on SMP machine: >>explain on 7.1.3 say it is a seq scan with cost 18837 >>explain on 7.2b2 say it is a seq scan 22.5 >> >>on single cpu 7.2b2 says: >>Index scan with cost 29 and it is very fast!! >> >>All platform are linux/i386 using redhat 7.2 and latest libraries >> >>I'm testing latest snapshot just now. >> >>thanks >> >> >> >>-- >>------------------------------------------------------- >>Giuseppe Tanzilli g.tanzilli@gruppocsf.com >>CSF Sistemi srl phone ++39 0775 7771 >>Via del Ciavattino >>Anagni FR >>Italy >> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >>http://www.postgresql.org/users-lounge/docs/faq.html >> > > -- ------------------------------------------------------- Giuseppe Tanzilli g.tanzilli@gruppocsf.com CSF Sistemi srl phone ++39 0775 7771 Via del Ciavattino Anagni FR Italy
...
> All platform are linux/i386 using redhat 7.2 and latest libraries
I'm guessing that one platform has "locale" enabled (or some combination
of multibyte parameters?), whereas the other does not. SMP will not make
a difference in configuration or optimizer choices.
The good news is that you have *one* machine which does what you want,
so we know that we can get the other one doing that too :)
- Thomas
(back on-list)
> You got it, I have locale anebled on all platform,
> but I see a different locale setting:
>
> it is working where I have LC_* = it_IT
> not working where I have LC_* = it_IT@euro
> what I can do ?
> I tried to start postgresql 7.2b2 with the working locale but nothing
> appened, I must initdb ??
I would try that. If you stay on list, then someone who actually *knows*
is likely to answer. I'm just guessing ;)
- Thomas
I did a fresh initdb after setting LC_* to it_IT but nothing seems to change bye Thomas Lockhart wrote: >(back on-list) > >>You got it, I have locale anebled on all platform, >>but I see a different locale setting: >> >>it is working where I have LC_* = it_IT >>not working where I have LC_* = it_IT@euro >>what I can do ? >>I tried to start postgresql 7.2b2 with the working locale but nothing >>appened, I must initdb ?? >> > >I would try that. If you stay on list, then someone who actually *knows* >is likely to answer. I'm just guessing ;) > > - Thomas > -- ------------------------------------------------------- Giuseppe Tanzilli g.tanzilli@gruppocsf.com CSF Sistemi srl phone ++39 0775 7771 Via del Ciavattino Anagni FR Italy
>> it is working where I have LC_* = it_IT
>> not working where I have LC_* = it_IT@euro
>> what I can do ?
>> I tried to start postgresql 7.2b2 with the working locale but nothing
>> appened, I must initdb ??
Yes. The database locale is determined at initdb time.
regards, tom lane