Slow search.. quite clueless

Yonatan Ben-Nes wrote:

Actually I even started to look on other solutions and maybe you can say
something about them also.. maybe they can help me:
1. Omega (From the Xapian project) - http://www.xapian.org/

You could certainly do this with Xapian and Omega. With only 5
million records it should be very quick.

The easiest approach would be to periodically dump the SQL tables
and build a new Xapian index which reflects the SQL database - you'd
probably want to customise the "dbi2omega" script in the Omega
distribution. This approach works particularly well if the tables
are updated in a batch fashion (one big weekly update, say).

Alternatively you could hook into whatever updates the SQL database
and get it to make corresponding updates to the Xapian index. That
has the advantage that they'll always be in step, but is probably
more work to set up.

The main drawback compared to doing everything in SQL is that you'd
have two systems to deal with rather than just one...

Cheers,
Olly

On 9/20/05, Yonatan Ben-Nes <da@canaan.co.il> wrote:

Hi all,

Im building a site where the users can search for products with up to 4
diffrent keywords which all MUST match to each product which found as a
result to the search.

I got 2 tables (which are relevant to the issue :)), one is the product
table (5 million rows) and the other is the keyword table which hold the
keywords of each product (60 million rows).

The scheme of the tables is as follows:

Table "public.product"
Column | Type | Modifiers
----------------------------+---------------+---------------------
product_id | text | not null
product_name | text | not null
retail_price | numeric(10,2) | not null
etc...
Indexes:
"product_product_id_key" UNIQUE, btree (product_id)

Table "public.keyword"
Column | Type | Modifiers
-------------+---------------+-----------
product_id | text | not null
keyword | text | not null
Indexes:
"keyword_keyword" btree (keyword)

The best query which I succeded to do till now is adding the keyword
table for each keyword searched for example if someone search for "belt"
& "black" & "pants" it will create the following query:

poweraise.com=# EXPLAIN ANALYZE SELECT

product_id,product_name,product_image_url,short_product_description,long_product_description,discount,discount_type,sale_price,retail_price
FROM product INNER JOIN keyword t1 USING(product_id) INNER JOIN keyword
t2 USING(product_id) INNER JOIN keyword t3 USING(product_id) WHERE
t1.keyword='belt' AND t2.keyword='black' AND t3.keyword='pants' LIMIT 13;

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=37734.15..39957.20 rows=13 width=578) (actual
time=969.798..1520.354 rows=6 loops=1)
-> Hash Join (cost=37734.15..3754162.82 rows=21733 width=578)
(actual time=969.794..1520.337 rows=6 loops=1)
Hash Cond: ("outer".product_id = "inner".product_id)
-> Nested Loop (cost=18867.07..2858707.34 rows=55309
width=612) (actual time=82.266..1474.018 rows=156 loops=1)
-> Hash Join (cost=18867.07..2581181.09 rows=55309
width=34) (actual time=82.170..1462.104 rows=156 loops=1)
Hash Cond: ("outer".product_id = "inner".product_id)
-> Index Scan using keyword_keyword on keyword t2
(cost=0.00..331244.43 rows=140771 width=17) (actual
time=0.033..1307.167 rows=109007 loops=1)
Index Cond: (keyword = 'black'::text)
-> Hash (cost=18851.23..18851.23 rows=6337
width=17) (actual time=16.145..16.145 rows=0 loops=1)
-> Index Scan using keyword_keyword on
keyword t1 (cost=0.00..18851.23 rows=6337 width=17) (actual
time=0.067..11.050 rows=3294 loops=1)
Index Cond: (keyword = 'belt'::text)
-> Index Scan using product_product_id_key on product
(cost=0.00..5.01 rows=1 width=578) (actual time=0.058..0.060 rows=1
loops=156)
Index Cond: (product.product_id = "outer".product_id)
-> Hash (cost=18851.23..18851.23 rows=6337 width=17) (actual
time=42.863..42.863 rows=0 loops=1)
-> Index Scan using keyword_keyword on keyword t3
(cost=0.00..18851.23 rows=6337 width=17) (actual time=0.073..36.120
rows=3932 loops=1)
Index Cond: (keyword = 'pants'::text)
Total runtime: 1521.441 ms
(17 rows)

Sometimes the query work fast even for 3 keywords but that doesnt help
me if at other times it take ages....

Hmm, JOIN on a Huge table with LIMIT. You may be suffering from
the same problem I had:

http://archives.postgresql.org/pgsql-performance/2005-07/msg00345.php

Tom came up with a patch which worked marvellous in my case:

http://archives.postgresql.org/pgsql-performance/2005-07/msg00352.php

Try applying this patch, it may solve your problem!

Regards,
Dawid

Olly Betts wrote:

Yonatan Ben-Nes wrote:

Actually I even started to look on other solutions and maybe you can say
something about them also.. maybe they can help me:
1. Omega (From the Xapian project) - http://www.xapian.org/

You could certainly do this with Xapian and Omega. With only 5
million records it should be very quick.

The easiest approach would be to periodically dump the SQL tables
and build a new Xapian index which reflects the SQL database - you'd
probably want to customise the "dbi2omega" script in the Omega
distribution. This approach works particularly well if the tables
are updated in a batch fashion (one big weekly update, say).

Alternatively you could hook into whatever updates the SQL database
and get it to make corresponding updates to the Xapian index. That
has the advantage that they'll always be in step, but is probably
more work to set up.

The main drawback compared to doing everything in SQL is that you'd
have two systems to deal with rather than just one...

Cheers,
Olly

Ok then ill try this option if the tsearch2 wont work fast enough for me
(hopefully it will :)).

Thanks alot,
Ben-Nes Yonatan

Yonatan Ben-Nes wrote:

Dawid Kuroczko wrote:

Hmm, JOIN on a Huge table with LIMIT. You may be suffering from
the same problem I had:

http://archives.postgresql.org/pgsql-performance/2005-07/msg00345.php

Tom came up with a patch which worked marvellous in my case:

http://archives.postgresql.org/pgsql-performance/2005-07/msg00352.php

Try applying this patch, it may solve your problem!

Regards,
Dawid

Great then ill check it if the tsearch2 wont work (testing in about 2-3
hours...).

please post your results here too (if possible) ;)

i'm very interested in your research (we are also having performance
problems, so i also thought about tsearch2...)

thanks,
gabor

Dawid Kuroczko wrote:

On 9/20/05, *Yonatan Ben-Nes* <da@canaan.co.il <mailto:da@canaan.co.il>>
wrote:

Hi all,

Im building a site where the users can search for products with up to 4
diffrent keywords which all MUST match to each product which found as a
result to the search.

I got 2 tables (which are relevant to the issue :)), one is the product
table (5 million rows) and the other is the keyword table which hold the
keywords of each product (60 million rows).

The scheme of the tables is as follows:

Table "public.product"
Column | Type | Modifiers
----------------------------+---------------+---------------------
product_id | text | not null
product_name | text | not null
retail_price | numeric(10,2) | not null
etc...
Indexes:
"product_product_id_key" UNIQUE, btree (product_id)

Table "public.keyword"
Column | Type | Modifiers
-------------+---------------+-----------
product_id | text | not null
keyword | text | not null
Indexes:
"keyword_keyword" btree (keyword)

The best query which I succeded to do till now is adding the keyword
table for each keyword searched for example if someone search for "belt"
& "black" & "pants" it will create the following query:

poweraise.com=# EXPLAIN ANALYZE SELECT
product_id,product_name,product_image_url,short_product_description,long_product_description,discount,discount_type,sale_price,retail_price
FROM product INNER JOIN keyword t1 USING(product_id) INNER JOIN keyword
t2 USING(product_id) INNER JOIN keyword t3 USING(product_id) WHERE
t1.keyword='belt' AND t2.keyword='black' AND t3.keyword='pants'
LIMIT 13;

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Limit (cost=37734.15..39957.20 rows=13 width=578) (actual
time=969.798..1520.354 rows=6 loops=1)
-> Hash Join (cost=37734.15..3754162.82 rows=21733 width=578)
(actual time=969.794..1520.337 rows=6 loops=1)
Hash Cond: ("outer".product_id = "inner".product_id)
-> Nested Loop (cost=18867.07..2858707.34 rows=55309
width=612) (actual time=82.266..1474.018 rows=156 loops=1)
-> Hash Join (cost=18867.07..2581181.09 rows=55309
width=34) (actual time=82.170..1462.104 rows=156 loops=1)
Hash Cond: ("outer".product_id =
"inner".product_id)
-> Index Scan using keyword_keyword on keyword t2
(cost=0.00..331244.43 rows=140771 width=17) (actual
time=0.033..1307.167 rows=109007 loops=1)
Index Cond: (keyword = 'black'::text)
-> Hash (cost=18851.23..18851.23 rows=6337
width=17) (actual time=16.145..16.145 rows=0 loops=1)
-> Index Scan using keyword_keyword on
keyword t1 (cost=0.00..18851.23 rows=6337 width=17) (actual
time=0.067..11.050 rows=3294 loops=1)
Index Cond: (keyword = 'belt'::text)
-> Index Scan using product_product_id_key on product
(cost=0.00..5.01 rows=1 width=578) (actual time=0.058..0.060 rows=1
loops=156)
Index Cond: (product.product_id =
"outer".product_id)
-> Hash (cost=18851.23..18851.23 rows=6337 width=17) (actual
time=42.863..42.863 rows=0 loops=1)
-> Index Scan using keyword_keyword on keyword t3
(cost=0.00..18851.23 rows=6337 width=17) (actual time=0.073..36.120
rows=3932 loops=1)
Index Cond: (keyword = 'pants'::text)
Total runtime: 1521.441 ms
(17 rows)

Sometimes the query work fast even for 3 keywords but that doesnt help
me if at other times it take ages....

Hmm, JOIN on a Huge table with LIMIT. You may be suffering from
the same problem I had:

http://archives.postgresql.org/pgsql-performance/2005-07/msg00345.php

Tom came up with a patch which worked marvellous in my case:

http://archives.postgresql.org/pgsql-performance/2005-07/msg00352.php

Try applying this patch, it may solve your problem!

Regards,
Dawid

Great then ill check it if the tsearch2 wont work (testing in about 2-3
hours...).

Thanks alot,
Ben-Nes Yonatan

G�bor Farkas wrote:

Yonatan Ben-Nes wrote:

Dawid Kuroczko wrote:

Hmm, JOIN on a Huge table with LIMIT. You may be suffering from
the same problem I had:

http://archives.postgresql.org/pgsql-performance/2005-07/msg00345.php

Tom came up with a patch which worked marvellous in my case:

http://archives.postgresql.org/pgsql-performance/2005-07/msg00352.php

Try applying this patch, it may solve your problem!

Regards,
Dawid

Great then ill check it if the tsearch2 wont work (testing in about
2-3 hours...).

please post your results here too (if possible) ;)

i'm very interested in your research (we are also having performance
problems, so i also thought about tsearch2...)

thanks,
gabor

No problem, ill do so :)

Regards,
Yonatan Ben-Nes

On Mon, 26 Sep 2005, Yonatan Ben-Nes wrote:

Hi again everyone,

Oleg I tried tsearch2 and happily it does work wonderfully for me returning
results extremly fast and actually its working even better then I wanted with
all of those neat features like: lexem, weight & stop words.

I got only one problem which is when I want the results to be ordered by a
diffrent field (like print INT field) it takes quite alot of time for it to
do it if the query can return lots of results (for example search for the
word "computer") and thats even if I limit the results.
The best way to improve its speed for such quereies (that I've found...) is
to create an index on the field which I want to order by and using it CLUSTER
the table, after the clustering I drop the the index so it won't be used when
I run queries with ORDER BY on that field, that seem to improve the speed, if
anyone got a better idea ill be glad to hear it.

what's your actual query ? have you tried multicolumn index ?

Anyway thanks alot everyone!
Ben-Nes Yonatan

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 Bartunov wrote:

On Mon, 26 Sep 2005, Yonatan Ben-Nes wrote:

Hi again everyone,

Oleg I tried tsearch2 and happily it does work wonderfully for me
returning results extremly fast and actually its working even better
then I wanted with all of those neat features like: lexem, weight &
stop words.

I got only one problem which is when I want the results to be ordered
by a diffrent field (like print INT field) it takes quite alot of time
for it to do it if the query can return lots of results (for example
search for the word "computer") and thats even if I limit the results.
The best way to improve its speed for such quereies (that I've
found...) is to create an index on the field which I want to order by
and using it CLUSTER the table, after the clustering I drop the the
index so it won't be used when I run queries with ORDER BY on that
field, that seem to improve the speed, if anyone got a better idea ill
be glad to hear it.

what's your actual query ? have you tried multicolumn index ?

Anyway thanks alot everyone!
Ben-Nes Yonatan

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

Hi Oleg,

I can't use a multicolumn index cause I already use on that table the
tsearch2 index, here is the query:

EXPLAIN ANALYZE SELECT product_id,final_price FROM product WHERE
keywords_vector @@ to_tsquery('cat') ORDER BY retail_price LIMIT 13;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4.02..4.03 rows=1 width=39) (actual time=367.627..367.654
rows=13 loops=1)
-> Sort (cost=4.02..4.03 rows=1 width=39) (actual
time=367.622..367.630 rows=13 loops=1)
Sort Key: retail_price
-> Index Scan using product_keywords_vector_idx on product
(cost=0.00..4.01 rows=1 width=39) (actual time=0.056..276.385 rows=14295
loops=1)
Index Cond: (keywords_vector @@ '\'cat\''::tsquery)
Total runtime: 370.916 ms
(6 rows)

Now this is the result after its already at the cache (made such a query
b4), the first time I ran this query it took few seconds...

Thanks as always :),
Ben-Nes Yonatan