pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hervé Piedvache <herve@elma.fr>
To: Oleg Bartunov <oleg@sai.msu.su>
Cc: pgsql-performance@postgresql.org
Subject: Re: Tsearch2 really slower than ilike ?
Date: Thu, 18 Nov 2004 10:27:00 +0100
Message-ID: <200411181027.00592.herve@elma.fr> (raw)
In-Reply-To: <Pine.GSO.4.61.0411172017120.18871@ra.sai.msu.su>
References: <200411161555.59144.herve@elma.fr>
	<200411171816.10794.herve@elma.fr>
	<Pine.GSO.4.61.0411172017120.18871@ra.sai.msu.su>

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



view thread (16+ messages)  latest in thread

Message-ID: <200411181027.00592.herve@elma.fr>
Permalink:  ../200411181027.00592.herve@elma.fr/
Also on:    postgresql.org/message-id/200411181027.00592.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, oleg@sai.msu.su
  Subject: Re: Tsearch2 really slower than ilike ?
  In-Reply-To: <200411181027.00592.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