Обсуждение: 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.