X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4DFD33A4017 for ; Thu, 18 Nov 2004 10:30:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20738-07 for ; Thu, 18 Nov 2004 10:30:14 +0000 (GMT) Received: from mailer.elma.loc (mail.elma.fr [213.41.14.138]) by svr1.postgresql.org (Postfix) with ESMTP id 0D99E3A3E96 for ; Thu, 18 Nov 2004 10:30:15 +0000 (GMT) Received: from mailer.elma.loc (localhost [127.0.0.1]) by localhost (Postfix) with ESMTP id A0284EC22B; Thu, 18 Nov 2004 11:33:23 +0100 (CET) Received: from zoot.elma.loc (herve.elma.fr [10.0.1.2]) by mailer.elma.loc (Postfix) with ESMTP id 5DA1AEC229; Thu, 18 Nov 2004 11:33:23 +0100 (CET) From: =?iso-8859-15?q?Herv=E9_Piedvache?= Organization: Elma =?iso-8859-15?q?Ing=E9nierie?= Informatique To: Oleg Bartunov Subject: Re: Tsearch2 really slower than ilike ? Date: Thu, 18 Nov 2004 11:30:13 +0100 User-Agent: KMail/1.7.1 Cc: pgsql-performance@postgresql.org References: <200411161555.59144.herve@elma.fr> <200411181027.00592.herve@elma.fr> In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 8bit Content-Disposition: inline Message-Id: <200411181130.13702.herve@elma.fr> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/278 X-Sequence-Number: 9232 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@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 -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902