Обсуждение: BUG #4351: Full text search performance

The following bug has been logged online:

Bug reference:      4351
Logged by:          Lawrence Cohan
Email address:      Lawrencec@1shoppingcart.com
PostgreSQL version: 8.3.3
Operating system:   Linux Red Hat 5.1
Description:        Full text search performance
Details:

-- We are about to use full text search on our 1.7 million rows products
table (described below) and to me it looks like either full text is not
working or I'm doing something wong as the EXPLAIN on all these queries
below shows that the FT indexes I created aren't used no matter what I tried
and with many diffrent searched string.
-- Is there anything wrong in the sequence below? Sory but I couldn't figure
it out by myself from FAQ or from the internet.

CREATE TABLE products
(
  id serial NOT NULL,
  product_name character varying(250) NOT NULL,
  product_price numeric NOT NULL,
  product_sku character varying(250),
  product_type_id integer NOT NULL,
  short_description character varying(250) NOT NULL,
  long_description text
  )

--Added FT indexes as documented for product_name and long_description
CREATE INDEX idx_ft_products_long_description ON products USING
gin(to_tsvector('english', long_description));
CREATE INDEX idx_ft_products_name ON products USING
gin(to_tsvector('english', product_name));
analyze products;

--tried the FT queries below:
EXPLAIN
SELECT product_name FROM products
WHERE to_tsvector(product_name) @@ to_tsquery('album');

SELECT long_description FROM products
WHERE to_tsvector(long_description) @@ to_tsquery('album');

--they seems to be much slower than the LIKE below:
--FT query - 45 seconds vs. 4 seconds for the one below
SELECT long_description FROM products
WHERE long_description like '%album%';

--FT query - 10-11 seconds vs. 1 second for the one below

SELECT product_name FROM products
WHERE lower(product_name) like '%album%';

"Lawrence Cohan" <Lawrencec@1shoppingcart.com> writes:
> -- Is there anything wrong in the sequence below? Sory but I couldn't figure
> it out by myself from FAQ or from the internet.

> --Added FT indexes as documented for product_name and long_description
> CREATE INDEX idx_ft_products_long_description ON products USING
> gin(to_tsvector('english', long_description));
> CREATE INDEX idx_ft_products_name ON products USING
> gin(to_tsvector('english', product_name));
> analyze products;

> --tried the FT queries below:
> EXPLAIN
> SELECT product_name FROM products
> WHERE to_tsvector(product_name) @@ to_tsquery('album');

That query isn't going to match that index.  You'd need to write

... WHERE to_tsvector('english', product_name) @@ to_tsquery('album');

Basically, you can't rely on a default TS configuration when using the
functional-index approach to text searching, because of the restriction
that index contents can't depend on mutable state.

            regards, tom lane

Wow - is that easy! How could I miss that when I thought I read all
documentation and knew that full-text search is catalog/language
dependent?=20

Many thanks and sorry for wasting your time with such a minor thing -
the difference is indeed amazing as the results are back in a few
hundreds of milliseconds on any searched string.

Best regards,
Lawrence Cohan.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Monday, August 11, 2008 9:31 PM
To: Lawrence Cohan
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4351: Full text search performance=20

"Lawrence Cohan" <Lawrencec@1shoppingcart.com> writes:
> -- Is there anything wrong in the sequence below? Sory but I couldn't
figure
> it out by myself from FAQ or from the internet.

> --Added FT indexes as documented for product_name and long_description

> CREATE INDEX idx_ft_products_long_description ON products USING
> gin(to_tsvector('english', long_description));
> CREATE INDEX idx_ft_products_name ON products USING
> gin(to_tsvector('english', product_name));
> analyze products;

> --tried the FT queries below:
> EXPLAIN
> SELECT product_name FROM products
> WHERE to_tsvector(product_name) @@ to_tsquery('album');

That query isn't going to match that index.  You'd need to write

... WHERE to_tsvector('english', product_name) @@ to_tsquery('album');

Basically, you can't rely on a default TS configuration when using the
functional-index approach to text searching, because of the restriction
that index contents can't depend on mutable state.

            regards, tom lane
Attention:
The information contained in this message and or attachments is intended on=
ly for the person or entity to which it is addressed and may =0D
contain confidential and/or privileged material.  Any review, retransmissio=
n, dissemination or other use of, or taking of any action in =0D
reliance upon, this information by persons or entities other than the inten=
ded recipient is prohibited. If you received this in error, please =0D
contact the sender and delete the material from any system and destroy any =
copies.