Re: Tsearch2 really slower than ilike ?

French encodings vs. Cyrillic encodings? Characters coming thru the mail in some encoding that don't get translated
properly.

His name is Herve Piedvache, where the 2nd 'e' in Herve is an accented character. It must somehow do weird things to
yourterminal when it's trying to map that into the encoding which you use.
 

Messages from you also come out in my mailer; lots of '1;2c1;2c' sequences (one - semi-colon - 2 - character-c and
repeat)

cheers,

--Tim

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Oleg
Bartunov
Sent: Thursday, November 18, 2004 11:34 AM
To: Herve Piedvache
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Tsearch2 really slower than ilike ?


1;2c1;2c1;2cBlin !

what's happenning with my terminal when I read messagess from this guy ?
I don't even know how to call him - I see just Herv?

     Oleg
1;2c1;2c1;2c1;2c
1;2cOn Thu, 18 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:

> Le Jeudi 18 Novembre 2004 10:37, Oleg Bartunov a ?crit :
>> Have you run 'vacuum analyze' ?
>
> Yep every night VACUUM FULL VERBOSE ANALYZE; of all the database !
>
>> 1;2c1;2c1;2c
>> 1;2c1;2c1;2cmy desktop is very simple PIII, 512 Mb RAM.
>> 1;2c1;2c11;2c1;2c1;2c;2c Oleg1;2c1;2c1;2c
>> 11;2c1;2c1;2c;2c1;2c1;2c
>
> YOU send strange caracters ! ;o)
>
>> 1;2c1;2c1;2cOn Thu, 18 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:
>>> Oleg,
>>>
>>> Le Mercredi 17 Novembre 2004 18:23, Oleg Bartunov a ?crit :
>>>>> Sorry but when I do your request I get :
>>>>> # select id_site from site where idx_site_name @@  'livejourn';
>>>>> ERROR:  type " " d1;2c1;2c1;2c1;2coes not exist
>>>>
>>>> no idea :) btw, what version of postgresql and OS you're running.
>>>> Could you try minimal test - check sql commands from tsearch2 sources,
>>>> some basic queries from tsearch2 documentation, tutorials.
>>>>
>>>> btw, your query should looks like
>>>> select id_site from site_rss where idx_site_name @@ 'livejourn';
>>>>                      ^^^^^^^^
>>>>
>>>> How did you run your queries at all ? I mean your first message about
>>>> poor tsearch2 performance.
>>>
>>> I don't know what happend yesterday ... it's running now ...
>>>
>>> You sent me :
>>> zz=# explain analyze select id_site from site_rss where idx_site_name
>>> @@  'livejourn';
>>>                                                              QUERY PLAN
>>> -------------------------------------------------------------------------
>>> ---------------------------------------------------------- Index Scan
>>> using ix_idx_site_name on site_rss  (cost=0.00..733.62 rows=184 width=4)
>>> (actual time=0.339..39.183 rows=1737 loops=1)
>>>     Index Cond: (idx_site_name @@ '\'livejourn\''::tsquery)
>>>     Filter: (idx_site_name @@ '\'livejourn\''::tsquery)
>>>   Total runtime: 40.997 ms
>>> (4 rows)
>>>
>>>> It's really fast ! So, I don't understand your problem.
>>>> I run query on my desktop machine, nothing special.
>>>
>>> I get this :
>>>                                                               QUERY PLAN
>>> -------------------------------------------------------------------------
>>> ---------------------------------------------------------------- Index
>>> Scan using ix_idx_site_name on site_rss s  (cost=0.00..574.19 rows=187
>>> width=24) (actual time=105.097..7157.277 rows=388 loops=1)
>>>   Index Cond: (idx_site_name @@ '\'livejourn\''::tsquery)
>>>   Filter: (idx_site_name @@ '\'livejourn\''::tsquery)
>>> Total runtime: 7158.576 ms
>>> (4 rows)
>>>
>>> With the ilike I get :
>>>                                                 QUERY PLAN
>>> -------------------------------------------------------------------------
>>> ----------------------------------- Seq Scan on site_rss s
>>> (cost=0.00..8360.23 rows=1 width=24) (actual time=8.195..879.440 rows=404
>>> loops=1)
>>>   Filter: (site_name ~~* '%livejourn%'::text)
>>> Total runtime: 882.600 ms
>>> (3 rows)
>>>
>>> I don't know what is your desktop ... but I'm using PostgreSQL 7.4.6, on
>>> Debian Woody with a PC Bi-PIII 933 Mhz and 1 Gb of memory ... the server
>>> is dedicated to this database ... !!
>>>
>>> I have no idea !
>>>
>>> Regards,
>>
>>      Regards,
>>          Oleg
>> _____________________________________________________________
>> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
>> Sternberg Astronomical Institute, Moscow University (Russia)
>> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
>> phone: +007(095)939-16-83, +007(095)939-23-83
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org