Re: Tsearch2 really slower than ilike ?
От | Oleg Bartunov |
---|---|
Тема | Re: Tsearch2 really slower than ilike ? |
Дата | |
Msg-id | Pine.GSO.4.61.0411181236490.18871@ra.sai.msu.su обсуждение исходный текст |
Ответ на | Re: Tsearch2 really slower than ilike ? (Hervé Piedvache <herve@elma.fr>) |
Ответы |
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-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@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---559023410-1271212614-1100770644=:18871--
В списке pgsql-performance по дате отправления: