Re: Tsearch2 really slower than ilike ?

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;2c1;2c Oleg
> 1;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 " " 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
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902