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 C19F53A4172 for ; Thu, 18 Nov 2004 09:27:19 +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 04413-02 for ; Thu, 18 Nov 2004 09:27:03 +0000 (GMT) Received: from mailer.elma.loc (mail.elma.fr [213.41.14.138]) by svr1.postgresql.org (Postfix) with ESMTP id 3C0EA3A4085 for ; Thu, 18 Nov 2004 09:27:04 +0000 (GMT) Received: from mailer.elma.loc (localhost [127.0.0.1]) by localhost (Postfix) with ESMTP id BFFEEEC229; Thu, 18 Nov 2004 10:30:11 +0100 (CET) Received: from zoot.elma.loc (herve.elma.fr [10.0.1.2]) by mailer.elma.loc (Postfix) with ESMTP id 922B4EC22F; Thu, 18 Nov 2004 10:30:10 +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 10:27:00 +0100 User-Agent: KMail/1.7.1 Cc: pgsql-performance@postgresql.org References: <200411161555.59144.herve@elma.fr> <200411171816.10794.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: <200411181027.00592.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/276 X-Sequence-Number: 9230 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, -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902