Re: Tsearch2 really slower than ilike ?

This message is in MIME format. The first part should be readable text,
while the remaining parts are likely unreadable without MIME-aware tools.

---559023410-1817792895-1100712215=:18871
Content-Type: TEXT/PLAIN; charset=koi8-r; format=flowed
Content-Transfer-Encoding: 8BIT

1;2c1;2c1;2cOn Wed, 17 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:

> Oleg,
>
> Sorry but when I do your request I get :
> # select id_site from site where idx_site_name @@  'livejourn';
> ERROR:  type " " does 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.

1;2c1;2c1;2c

> What is this ?
>
> (private: I don't know what happend with my mail, but I do nothing special to
> disturb the contains when I'm writting to you ...)
>
> Le Mardi 16 Novembre 2004 22:13, Oleg Bartunov a ?crit :
>> ok, I downloaded dump of table and here is what I found:
>>
>> zz=# select count(*) from tt;
>> count
>> --------
>> 183956
>> (1 row)
>>
>> zz=# select * from stat('select tt from tt') order by ndoc desc, nentry
>> desc,wo
>> rd limit 10;
>> word | ndoc | nentry
>> --------------+-------+--------
>> blog | 12710 | 12835
>> weblog | 4857 | 4859
>> news | 4402 | 4594
>> life | 4136 | 4160
>> world | 1980 | 1986
>> journal | 1882 | 1883
>> livejourn | 1737 | 1737
>> thought | 1669 | 1677
>> web | 1154 | 1161
>> scotsman.com | 1138 | 1138
>> (10 rows)
>>
>> zz=# explain analyze select tt from tt where tt @@ 'blog';
>> QUERY PLAN
>> ---------------------------------------------------------------------------
>> ------------------------------------------- Index Scan using tt_idx on tt
>> (cost=0.00..728.83 rows=184 width=32) (actual time=0.047..141.110
>> rows=12710 loops=1) Index Cond: (tt @@ '\'blog\''::tsquery)
>> Filter: (tt @@ '\'blog\''::tsquery)
>> Total runtime: 154.105 ms
>> (4 rows)
>>
>> It's really fast ! So, I don't understand your problem.
>> I run query on my desktop machine, nothing special.
>>
>>
>> Oleg
>>
>> On Tue, 16 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:
>>> Hi,
>>>
>>> I'm completly dispointed with Tsearch2 ...
>>>
>>> I have a table like this :
>>> Table "public.site"
>>> Column | Type |
>>> Modifiers
>>> ---------------+-----------------------------+---------------------------
>>> ------------------------------------ id_site | integer
>>> | not null default
>>> nextval('public.site_id_site_seq'::text)
>>> site_name | text |
>>> site_url | text |
>>> url | text |
>>> language | text |
>>> datecrea | date | default now()
>>> id_category | integer |
>>> time_refresh | integer |
>>> active | integer |
>>> error | integer |
>>> description | text |
>>> version | text |
>>> idx_site_name | tsvector |
>>> lastcheck | date |
>>> lastupdate | timestamp without time zone |
>>> Indexes:
>>> "site_id_site_key" unique, btree (id_site)
>>> "ix_idx_site_name" gist (idx_site_name)
>>> Triggers:
>>> tsvectorupdate_site_name BEFORE INSERT OR UPDATE ON site FOR EACH ROW
>>> EXECUTE PROCEDURE tsearch2('idx_site_name', 'site_name')
>>>
>>> I have 183 956 records in the database ...
>>>
>>> SELECT s.site_name, s.id_site, s.description, s.site_url,
>>> case when exists (select id_user
>>> from user_choice u
>>> where u.id_site=s.id_site
>>> and u.id_user = 1)
>>> then 1 else 0 end as bookmarked
>>> FROM site s
>>> WHERE s.idx_site_name @@ to_tsquery('atari');
>>>
>>> Explain Analyze :
>>> QUERY PLAN
>>> -------------------------------------------------------------------------
>>> ----------------------------------------------------------------- Index
>>> Scan using ix_idx_site_name on site s (cost=0.00..1202.12 rows=184
>>> width=158) (actual time=4687.674..4698.422 rows=1 loops=1)
>>> Index Cond: (idx_site_name @@ '\'atari\''::tsquery)
>>> Filter: (idx_site_name @@ '\'atari\''::tsquery)
>>> SubPlan
>>> -> Seq Scan on user_choice u (cost=0.00..3.46 rows=1 width=4)
>>> (actual time=0.232..0.232 rows=0 loops=1)
>>> Filter: ((id_site = $0) AND (id_user = 1))
>>> Total runtime: 4698.608 ms
>>>
>>> First time I run the request I have a result in about 28 seconds.
>>>
>>> SELECT s.site_name, s.id_site, s.description, s.site_url,
>>> case when exists (select id_user
>>> from user_choice u
>>> where u.id_site=s.id_site
>>> and u.id_user = 1)
>>> then 1 else 0 end as bookmarked
>>> FROM site_rss s
>>> WHERE s.site_name ilike '%atari%'
>>>
>>> QUERY PLAN
>>> -------------------------------------------------------------------------
>>> --------------------------------------- Seq Scan on site_rss s
>>> (cost=0.00..11863.16 rows=295 width=158) (actual time=17.414..791.937
>>> rows=12 loops=1)
>>> Filter: (site_name ~~* '%atari%'::text)
>>> SubPlan
>>> -> Seq Scan on user_choice u (cost=0.00..3.46 rows=1 width=4)
>>> (actual time=0.222..0.222 rows=0 loops=12)
>>> Filter: ((id_site = $0) AND (id_user = 1))
>>> Total runtime: 792.099 ms
>>>
>>> First time I run the request I have a result in about 789 miliseconds
>>> !!???
>>>
>>> I'm using PostgreSQL v7.4.6 with a Bi-Penitum III 933 Mhz and 1 Gb of
>>> RAM.
>>>
>>> Any idea ... ? For the moment I'm going back to use the ilike solution
>>> ... but I was really thinking that Tsearch2 could be a better solution
>>> ...
>>>
>>> Regards,
>>
>> Regards,
>> Oleg
>> _____________________________________________________________
>> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
>> Sternberg Astronomical Institute, Moscow University (Russia)
>> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
>> phone: +007(095)939-16-83, +007(095)939-23-83
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 9: the planner will ignore your desire to choose an index scan if your
>> joining column's datatypes do not match
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---559023410-1817792895-1100712215=:18871--