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-1271212614-1100770644=:18871
Content-Type: TEXT/PLAIN; charset=koi8-r; format=flowed
Content-Transfer-Encoding: 8BIT

Have you run 'vacuum analyze' ?
1;2c1;2c1;2c
1;2c1;2c1;2cmy desktop is very simple PIII, 512 Mb RAM.
1;2c1;2c1;2c Oleg
1;2c1;2c1;2c
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 " " 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.
>
> 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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---559023410-1271212614-1100770644=:18871--