pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hervé Piedvache <herve@elma.fr>
To: pgsql-performance@postgresql.org
Subject: Tsearch2 really slower than ilike ?
Date: Tue, 16 Nov 2004 15:55:58 +0100
Message-ID: <200411161555.59144.herve@elma.fr> (raw)

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



view thread (16+ messages)  latest in thread

Message-ID: <200411161555.59144.herve@elma.fr>
Permalink:  ../200411161555.59144.herve@elma.fr/
Also on:    postgresql.org/message-id/200411161555.59144.herve@elma.fr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: herve@elma.fr
  Subject: Re: Tsearch2 really slower than ilike ?
  In-Reply-To: <200411161555.59144.herve@elma.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox