[PERFORM] tsearch2 headline and postgresql.conf
Hi folks, I'm not sure if this is the right place for this but thought I'd ask. I'm relateively new to postgres having only used it on 3 projects and am just delving into the setup and admin for the second time. I decided to try tsearch2 for this project's search requirements but am having trouble attaining adequate performance. I think I've nailed it down to trouble with the headline() function in tsearch2. In short, there is a crawler that grabs HTML docs and places them in a database. The search is done using tsearch2 pretty much installed according to instructions. I have read a couple online guides suggested by this list for tuning the postgresql.conf file. I only made modest adjustments because I'm not working with top-end hardware and am still uncertain of the actual impact of the different paramenters. I've been learning 'explain' and over the course of reading I have done enough query tweaking to discover the source of my headache seems to be headline(). On a query of 429 documents, of which the avg size of the stripped down document as stored is 21KB, and the max is 518KB (an anomaly), tsearch2 performs exceptionally well returning most queries in about 100ms. On the other hand, following the tsearch2 guide which suggests returning that first portion as a subquery and then generating the headline() from those results, I see the query increase to 4 seconds! This seems to be directly related to document size. If I filter out that 518KB doc along with some 100KB docs by returning "substring( stripped_text FROM 0 FOR 5) AS stripped_text" I decrease the time to 1.4 seconds, but increase the risk of not getting a headline. Seeing as how this problem is directly tied to document size, I'm wondering if there are any specific settings in postgresql.conf that may help, or is this just a fact of life for the headline() function? Or, does anyone know what the problem is and how to overcome it? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] tsearch2 headline and postgresql.conf
Oleg Bartunov wrote:
You didn't provides us any query with explain analyze.
Just to make sure you're fine.
Oleg
On Sun, 22 Jan 2006, [EMAIL PROTECTED] wrote:
Hi folks,
I'm not sure if this is the right place for this but thought I'd
ask. I'm relateively new to postgres having only used it on 3
projects and am just delving into the setup and admin for the second
time.
I decided to try tsearch2 for this project's search requirements but
am having trouble attaining adequate performance. I think I've
nailed it down to trouble with the headline() function in tsearch2.
In short, there is a crawler that grabs HTML docs and places them in
a database. The search is done using tsearch2 pretty much installed
according to instructions. I have read a couple online guides
suggested by this list for tuning the postgresql.conf file. I only
made modest adjustments because I'm not working with top-end hardware
and am still uncertain of the actual impact of the different
paramenters.
I've been learning 'explain' and over the course of reading I have
done enough query tweaking to discover the source of my headache
seems to be headline().
On a query of 429 documents, of which the avg size of the stripped
down document as stored is 21KB, and the max is 518KB (an anomaly),
tsearch2 performs exceptionally well returning most queries in about
100ms.
On the other hand, following the tsearch2 guide which suggests
returning that first portion as a subquery and then generating the
headline() from those results, I see the query increase to 4 seconds!
This seems to be directly related to document size. If I filter out
that 518KB doc along with some 100KB docs by returning "substring(
stripped_text FROM 0 FOR 5) AS stripped_text" I decrease the time
to 1.4 seconds, but increase the risk of not getting a headline.
Seeing as how this problem is directly tied to document size, I'm
wondering if there are any specific settings in postgresql.conf that
may help, or is this just a fact of life for the headline()
function? Or, does anyone know what the problem is and how to
overcome it?
Regards,
Oleg
_
Hi Oleg,
Thanks for taking time to look at this. Pardon my omission, I was
writing that email rather late at night.
The following results from 'explain analyze' are from my
devlopment machine which is a dual PIII 600MHz running Debian
Linux and Postgres 8.1.2. 512 MB RAM. The production machine
yields similar results but it is a virtual server so the
resources are rather unpredictable. It is a quad processor and
has a larger result set in it's DB.
The original query is:
explain analyze
SELECT url, title, headline(stripped_text,q,
'MaxWords=75, MinWords=25,
StartSel=!!!REPLACE_ME!!!,StopSel=!!!/REPLACE_ME!!!'),
rank, to_char(timezone('CST', date_last_changed), 'DD Mon ') AS
date_last_changed
FROM
( SELECT url_id, url, title, stripped_text, date_last_changed, q,
rank(index_text, q) AS rank
FROM (web_page w LEFT JOIN url u USING (url_id)),
to_tsquery('big&search') AS q
WHERE (index_text <> '') AND (index_text @@ q) AND (w.url_id NOT IN (1,2))
AND (url NOT LIKE '%badurl.com%')
ORDER BY rank DESC, date_last_changed DESC
LIMIT 10 OFFSET 0
) AS useless
;
...and the resultant output of EXPLAIN ANALYZE is:
Subquery Scan useless (cost=8.02..8.04 rows=1 width=624) (actual
time=769.131..2769.320 rows=10 loops=1)
-> Limit (cost=8.02..8.02 rows=1 width=282) (actual
time=566.798..566.932 rows=10 loops=1)
-> Sort (cost=8.02..8.02 rows=1 width=282) (actual
time=566.792..566.870 rows=10 loops=1)
Sort Key: rank(w.index_text, q.q), w.date_last_changed
-> Nested Loop (cost=2.00..8.01 rows=1 width=282)
(actual time=4.068..563.128 rows=178 loops=1)
-> Nested Loop (cost=2.00..4.96 rows=1 width=221)
(actual time=3.179..388.610 rows=179 loops=1)
-> Function Scan on q (cost=0.00..0.01
rows=1 width=32) (actual time=0.025..0.028 rows=1 loops=1)
-> Bitmap Heap Scan on web_page w
(cost=2.00..4.94 rows=1 width=189) (actual time=3.123..387.547 rows=179
loops=1)
Filter: ((w.index_text <> ''::tsvector)
AND (w.url_id <> 1) AND (w.url_id <> 2) AND (w.index_text @@ "outer".q))
-> Bitmap Index Scan on
idx_index_text (cost=0.00..2.00 rows=1 width=0) (actual
time=1.173..1.173 rows=277 loops=1)
Index Cond: (w.index_text @@
"outer".q)
-> Index Scan using pk_url on url u
(cost=0.00..3.03 rows=1 width=65) (actual time=0.044..0.049 rows=1
loops=179)
Index Cond: ("outer".url_id = u.url_id)
Filter: (url !~~ '%badurl.com%'::text)
Total runtime: 2771.023 ms
(15 rows)
-
[PERFORM] Why do my hash joins turn to nested loops?
ec_uid = 4) -> Index Scan using type_pkey on type (cost=0.00..0.27 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=3555) Index Cond: ((logical.type)::integer = type.uid) -> Index Scan using company_pkey on company (cost=0.00..0.27 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=3555) Index Cond: (model.company_uid = company.uid) Total runtime: 41773.972 ms (33 rows) -- Ian Smith www.ian.org -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Why do my hash joins turn to nested loops?
On Thu, 21 Aug 2008, Tom Lane wrote: > I think you need to raise from_collapse_limit and/or > join_collapse_limit. Ahah, that was it.. a much simpler solution than I was fearing. I had already re-written the queries to get around it, but ran into another snag with that method, so this was good timing. Thanks! -- Ian Smith www.ian.org -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Sun performance - Major discovery!
In message <[EMAIL PROTECTED]>, Jeff writes: I'll go run the regression test suite with my gcc -O2 pg and the suncc pg. See if they pass the test. My default set of gcc optimization flags is: -O3 -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -falign-functions -mcpu=i686 -march=i686 Obviously the last two flags product CPU specific code, so would have to differ...autoconf is always possible, but so is just lopping them off. I have found these flags to produce faster code that a simple -O2, but I understand the exact combination which is best for you is code-dependent. Of course, if you are getting really excited, you can use -fbranch-probabilities, but as you will see if you investigate that requires some profiling information, so is not very easy to actually practically use. -Seth Robertson ---(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
