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 4379F3A3EDB for ; Tue, 16 Nov 2004 14:56:08 +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 52381-10 for ; Tue, 16 Nov 2004 14:55:58 +0000 (GMT) Received: from mailer.elma.loc (mail.elma.fr [213.41.14.138]) by svr1.postgresql.org (Postfix) with ESMTP id 252963A3E2D for ; Tue, 16 Nov 2004 14:56:01 +0000 (GMT) Received: from mailer.elma.loc (localhost [127.0.0.1]) by localhost (Postfix) with ESMTP id 4D0AEEC1EF for ; Tue, 16 Nov 2004 15:59:13 +0100 (CET) Received: from zoot.elma.loc (herve.elma.fr [10.0.1.2]) by mailer.elma.loc (Postfix) with ESMTP id 33E2EEC139 for ; Tue, 16 Nov 2004 15:59:13 +0100 (CET) From: =?iso-8859-15?q?Herv=E9_Piedvache?= Organization: Elma =?iso-8859-15?q?Ing=E9nierie?= Informatique To: pgsql-performance@postgresql.org Subject: Tsearch2 really slower than ilike ? Date: Tue, 16 Nov 2004 15:55:58 +0100 User-Agent: KMail/1.7.1 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 8bit Content-Disposition: inline Message-Id: <200411161555.59144.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/221 X-Sequence-Number: 9175 Hi, I'm completly dispointed with Tsearch2 ... I have a table like this : Table "public.site" Column | Type | Modifiers ---------------+-----------------------------+--------------------------------------------------------------- id_site | integer | not null default nextval('public.site_id_site_seq'::text) site_name | text | site_url | text | url | text | language | text | datecrea | date | default now() id_category | integer | time_refresh | integer | active | integer | error | integer | description | text | version | text | idx_site_name | tsvector | lastcheck | date | lastupdate | timestamp without time zone | Indexes: "site_id_site_key" unique, btree (id_site) "ix_idx_site_name" gist (idx_site_name) Triggers: tsvectorupdate_site_name BEFORE INSERT OR UPDATE ON site FOR EACH ROW EXECUTE PROCEDURE tsearch2('idx_site_name', 'site_name') I have 183 956 records in the database ... SELECT s.site_name, s.id_site, s.description, s.site_url, case when exists (select id_user from user_choice u where u.id_site=s.id_site and u.id_user = 1) then 1 else 0 end as bookmarked FROM site s WHERE s.idx_site_name @@ to_tsquery('atari'); Explain Analyze : QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------ Index Scan using ix_idx_site_name on site s (cost=0.00..1202.12 rows=184 width=158) (actual time=4687.674..4698.422 rows=1 loops=1) Index Cond: (idx_site_name @@ '\'atari\''::tsquery) Filter: (idx_site_name @@ '\'atari\''::tsquery) SubPlan -> Seq Scan on user_choice u (cost=0.00..3.46 rows=1 width=4) (actual time=0.232..0.232 rows=0 loops=1) Filter: ((id_site = $0) AND (id_user = 1)) Total runtime: 4698.608 ms First time I run the request I have a result in about 28 seconds. SELECT s.site_name, s.id_site, s.description, s.site_url, case when exists (select id_user from user_choice u where u.id_site=s.id_site and u.id_user = 1) then 1 else 0 end as bookmarked FROM site_rss s WHERE s.site_name ilike '%atari%' QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Seq Scan on site_rss s (cost=0.00..11863.16 rows=295 width=158) (actual time=17.414..791.937 rows=12 loops=1) Filter: (site_name ~~* '%atari%'::text) SubPlan -> Seq Scan on user_choice u (cost=0.00..3.46 rows=1 width=4) (actual time=0.222..0.222 rows=0 loops=12) Filter: ((id_site = $0) AND (id_user = 1)) Total runtime: 792.099 ms First time I run the request I have a result in about 789 miliseconds !!??? I'm using PostgreSQL v7.4.6 with a Bi-Penitum III 933 Mhz and 1 Gb of RAM. Any idea ... ? For the moment I'm going back to use the ilike solution ... but I was really thinking that Tsearch2 could be a better solution ... Regards, -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902