From: Oleg Bartunov <oleg@sai.msu.su>
To: Hervé Piedvache <herve@elma.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Tsearch2 really slower than ilike ?
Date: Thu, 18 Nov 2004 13:34:20 +0300 (MSK)
Message-ID: <Pine.GSO.4.61.0411181332500.18871@ra.sai.msu.su> (raw)
In-Reply-To: <200411181130.13702.herve@elma.fr>
References: <200411161555.59144.herve@elma.fr>
<200411181027.00592.herve@elma.fr>
<Pine.GSO.4.61.0411181236490.18871@ra.sai.msu.su>
<200411181130.13702.herve@elma.fr>
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--
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: oleg@sai.msu.su, herve@elma.fr
Subject: Re: Tsearch2 really slower than ilike ?
In-Reply-To: <Pine.GSO.4.61.0411181332500.18871@ra.sai.msu.su>
References: <200411161555.59144.herve@elma.fr> <200411181027.00592.herve@elma.fr> <Pine.GSO.4.61.0411181236490.18871@ra.sai.msu.su> <200411181130.13702.herve@elma.fr> <Pine.GSO.4.61.0411181332500.18871@ra.sai.msu.su>
* 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