Thread: Tsearch2 really slower than ilike ?
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
On Tue, Nov 16, 2004 at 03:55:58PM +0100, Herv� Piedvache wrote:
> WHERE s.idx_site_name @@ to_tsquery('atari');
How much text does each site_name field contain? From the field
name I'd guess only a few words. Based on my own experience, if
the fields were documents containing thousands of words then I'd
expect tsearch2 to be faster than ILIKE by an order of magnitude
or more.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Michael,
Le Mardi 16 Novembre 2004 16:32, Michael Fuhr a écrit :
> On Tue, Nov 16, 2004 at 03:55:58PM +0100, Hervé Piedvache wrote:
> > WHERE s.idx_site_name @@ to_tsquery('atari');
>
> How much text does each site_name field contain? From the field
> name I'd guess only a few words. Based on my own experience, if
> the fields were documents containing thousands of words then I'd
> expect tsearch2 to be faster than ILIKE by an order of magnitude
> or more.
Yes site name ... is company names or web site name ... so not many word in
each record ... but I don't understand why more words are more efficient than
few words ?? sorry ...
Regards,
--
Hervé Piedvache
Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902
>>or more. >> >> > >Yes site name ... is company names or web site name ... so not many word in >each record ... but I don't understand why more words are more efficient than >few words ?? sorry ... > > Well there are a couple of reasons but the easiest one is index size. An ILIKE btree index is in general going to be much smaller than a gist index. The smaller the index the faster it is searched. Sincerely, Joshua D. Drake >Regards, > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Attachment
> > 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 ... > > > Well I would be curious about what happens the second time you run the query. The first time is kind of a bad example because it has to push the index into ram. Sincerely, Joshua D. Drake >Regards, > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL
Attachment
could you provide me a dump of your table (just id and tsvector columns),
so I could try on my computer. Also, plain query (simple and clean) which
demonstrated your problem would be preferred next time !
Oleg
On Tue, 16 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:
> Michael,
>
> Le Mardi 16 Novembre 2004 16:32, Michael Fuhr a ?crit :
>> On Tue, Nov 16, 2004 at 03:55:58PM +0100, Herv? Piedvache wrote:
>>> WHERE s.idx_site_name @@ to_tsquery('atari');
>>
>> How much text does each site_name field contain? From the field
>> name I'd guess only a few words. Based on my own experience, if
>> the fields were documents containing thousands of words then I'd
>> expect tsearch2 to be faster than ILIKE by an order of magnitude
>> or more.
>
> Yes site name ... is company names or web site name ... so not many word in
> each record ... but I don't understand why more words are more efficient than
> few words ?? sorry ...
>
> 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
Le Mardi 16 Novembre 2004 17:06, Joshua D. Drake a écrit : > > 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 ... > > Well I would be curious about what happens the second time you run the > query. > The first time is kind of a bad example because it has to push the index > into ram. The second time is really quicker yes ... about 312 miliseconds ... But for each search I have after it take about 3 or 4 seconds ... So what can I do ? Regards, -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902
On Tue, 16 Nov 2004, Joshua D. Drake wrote:
>
>>> or more.
>>>
>>
>> Yes site name ... is company names or web site name ... so not many word in
>> each record ... but I don't understand why more words are more efficient
>> than few words ?? sorry ...
>>
> Well there are a couple of reasons but the easiest one is index size.
> An ILIKE btree index is in general going to be much smaller than a gist
> index.
> The smaller the index the faster it is searched.
for single word queries @@ should have the same performance as ilike with
index disabled and better for complex queries.
>
> Sincerely,
>
> Joshua D. Drake
>
>
>
>> 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
ok, I downloaded dump of table and here is what I found:
zz=# select count(*) from tt;
count
--------
183956
(1 row)
zz=# select * from stat('select tt from tt') order by ndoc desc, nentry
desc,wo
rd limit 10;
word | ndoc | nentry
--------------+-------+--------
blog | 12710 | 12835
weblog | 4857 | 4859
news | 4402 | 4594
life | 4136 | 4160
world | 1980 | 1986
journal | 1882 | 1883
livejourn | 1737 | 1737
thought | 1669 | 1677
web | 1154 | 1161
scotsman.com | 1138 | 1138
(10 rows)
zz=# explain analyze select tt from tt where tt @@ 'blog';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Index Scan using tt_idx on tt (cost=0.00..728.83 rows=184 width=32) (actual time=0.047..141.110 rows=12710 loops=1)
Index Cond: (tt @@ '\'blog\''::tsquery)
Filter: (tt @@ '\'blog\''::tsquery)
Total runtime: 154.105 ms
(4 rows)
It's really fast ! So, I don't understand your problem.
I run query on my desktop machine, nothing special.
Oleg
On Tue, 16 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:
> 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,
>
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
Oleg,
Sorry but when I do your request I get :
# select id_site from site where idx_site_name @@ 'livejourn';
ERROR: type " " does not exist
What is this ?
(private: I don't know what happend with my mail, but I do nothing special to
disturb the contains when I'm writting to you ...)
Le Mardi 16 Novembre 2004 22:13, Oleg Bartunov a écrit :
> ok, I downloaded dump of table and here is what I found:
>
> zz=# select count(*) from tt;
> count
> --------
> 183956
> (1 row)
>
> zz=# select * from stat('select tt from tt') order by ndoc desc, nentry
> desc,wo
> rd limit 10;
> word | ndoc | nentry
> --------------+-------+--------
> blog | 12710 | 12835
> weblog | 4857 | 4859
> news | 4402 | 4594
> life | 4136 | 4160
> world | 1980 | 1986
> journal | 1882 | 1883
> livejourn | 1737 | 1737
> thought | 1669 | 1677
> web | 1154 | 1161
> scotsman.com | 1138 | 1138
> (10 rows)
>
> zz=# explain analyze select tt from tt where tt @@ 'blog';
> QUERY PLAN
> ---------------------------------------------------------------------------
>------------------------------------------- Index Scan using tt_idx on tt
> (cost=0.00..728.83 rows=184 width=32) (actual time=0.047..141.110
> rows=12710 loops=1) Index Cond: (tt @@ '\'blog\''::tsquery)
> Filter: (tt @@ '\'blog\''::tsquery)
> Total runtime: 154.105 ms
> (4 rows)
>
> It's really fast ! So, I don't understand your problem.
> I run query on my desktop machine, nothing special.
>
>
> Oleg
>
> On Tue, 16 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:
> > 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,
>
> 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 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
--
Hervé Piedvache
Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902
This message is in MIME format. The first part should be readable text,
while the remaining parts are likely unreadable without MIME-aware tools.
---559023410-1817792895-1100712215=:18871
Content-Type: TEXT/PLAIN; charset=koi8-r; format=flowed
Content-Transfer-Encoding: 8BIT
1;2c1;2c1;2cOn Wed, 17 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:
> Oleg,
>
> 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.
1;2c1;2c1;2c
> What is this ?
>
> (private: I don't know what happend with my mail, but I do nothing special to
> disturb the contains when I'm writting to you ...)
>
> Le Mardi 16 Novembre 2004 22:13, Oleg Bartunov a ?crit :
>> ok, I downloaded dump of table and here is what I found:
>>
>> zz=# select count(*) from tt;
>> count
>> --------
>> 183956
>> (1 row)
>>
>> zz=# select * from stat('select tt from tt') order by ndoc desc, nentry
>> desc,wo
>> rd limit 10;
>> word | ndoc | nentry
>> --------------+-------+--------
>> blog | 12710 | 12835
>> weblog | 4857 | 4859
>> news | 4402 | 4594
>> life | 4136 | 4160
>> world | 1980 | 1986
>> journal | 1882 | 1883
>> livejourn | 1737 | 1737
>> thought | 1669 | 1677
>> web | 1154 | 1161
>> scotsman.com | 1138 | 1138
>> (10 rows)
>>
>> zz=# explain analyze select tt from tt where tt @@ 'blog';
>> QUERY PLAN
>> ---------------------------------------------------------------------------
>> ------------------------------------------- Index Scan using tt_idx on tt
>> (cost=0.00..728.83 rows=184 width=32) (actual time=0.047..141.110
>> rows=12710 loops=1) Index Cond: (tt @@ '\'blog\''::tsquery)
>> Filter: (tt @@ '\'blog\''::tsquery)
>> Total runtime: 154.105 ms
>> (4 rows)
>>
>> It's really fast ! So, I don't understand your problem.
>> I run query on my desktop machine, nothing special.
>>
>>
>> Oleg
>>
>> On Tue, 16 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:
>>> 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,
>>
>> 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 9: the planner will ignore your desire to choose an index scan if your
>> joining column's datatypes do not match
>
>
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
---559023410-1817792895-1100712215=:18871--
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
This message is in MIME format. The first part should be readable text,
while the remaining parts are likely unreadable without MIME-aware tools.
---559023410-1271212614-1100770644=:18871
Content-Type: TEXT/PLAIN; charset=koi8-r; format=flowed
Content-Transfer-Encoding: 8BIT
Have you run 'vacuum analyze' ?
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
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
---559023410-1271212614-1100770644=:18871--
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
This message is in MIME format. The first part should be readable text,
while the remaining parts are likely unreadable without MIME-aware tools.
---559023410-1857409239-1100774060=:18871
Content-Type: TEXT/PLAIN; charset=koi8-r; format=flowed
Content-Transfer-Encoding: 8BIT
1;2c1;2c1;2cBlin !
what's happenning with my terminal when I read messagess from this guy ?
I don't even know how to call him - I see just Herv?
Oleg
1;2c1;2c1;2c1;2c
1;2cOn Thu, 18 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:
> 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;2c11;2c1;2c1;2c;2c Oleg1;2c1;2c1;2c
>> 11;2c1;2c1;2c;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 " " d1;2c1;2c1;2c1;2coes 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
>
>
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
---559023410-1857409239-1100774060=:18871--
French encodings vs. Cyrillic encodings? Characters coming thru the mail in some encoding that don't get translated
properly.
His name is Herve Piedvache, where the 2nd 'e' in Herve is an accented character. It must somehow do weird things to
yourterminal when it's trying to map that into the encoding which you use.
Messages from you also come out in my mailer; lots of '1;2c1;2c' sequences (one - semi-colon - 2 - character-c and
repeat)
cheers,
--Tim
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Oleg
Bartunov
Sent: Thursday, November 18, 2004 11:34 AM
To: Herve Piedvache
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Tsearch2 really slower than ilike ?
1;2c1;2c1;2cBlin !
what's happenning with my terminal when I read messagess from this guy ?
I don't even know how to call him - I see just Herv?
Oleg
1;2c1;2c1;2c1;2c
1;2cOn Thu, 18 Nov 2004, [iso-8859-15] Herv? Piedvache wrote:
> 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;2c11;2c1;2c1;2c;2c Oleg1;2c1;2c1;2c
>> 11;2c1;2c1;2c;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 " " d1;2c1;2c1;2c1;2coes 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
>
>
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 6: Have you searched our list archives?
http://archives.postgresql.org