The following bug has been logged online:
Bug reference: 4351
Logged by: Lawrence Cohan
Email address: [EMAIL PROTECTED]
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%';
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
