Re: Tsearch2 really slower than ilike ?
От | Oleg Bartunov |
---|---|
Тема | Re: Tsearch2 really slower than ilike ? |
Дата | |
Msg-id | Pine.GSO.4.61.0411181332500.18871@ra.sai.msu.su обсуждение исходный текст |
Ответ на | Re: Tsearch2 really slower than ilike ? (Hervé Piedvache <herve@elma.fr>) |
Список | pgsql-performance |
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-1857409239-1100774060=:18871 Content-Type: TEXT/PLAIN; charset=koi8-r; format=flowed Content-Transfer-Encoding: 8BIT 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 ---559023410-1857409239-1100774060=:18871--
В списке pgsql-performance по дате отправления: