Kris, could you post 'explain analyze' output ? Also, could you disable index usage (set enable_indexscan=off) and rerun search using tsearch2 ?
also, could you run 'stat' function to see frequency distribution of words. See http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes for details. Oleg On Fri, 24 Sep 2004, Kris Kiger wrote: > Hi all. I am doing some work with tsearch2 and am not sure what to > expect out of it, performance wise. Here is my setup: > > Table "public.product" > Column | Type | Modifiers > -------------+----------+------------------------------------------------- > description | text | > product_id | integer | default nextval('product_product_id_seq'::text) > vector | tsvector | > Indexes: > "vector_idx" gist (vector) > Triggers: > tsvectorupdate BEFORE INSERT OR UPDATE ON product FOR EACH ROW EXECUTE PROCEDURE > tsearch2('vector', 'description') > > This table has 3,000,000 rows in it. Each description field has roughly 50 > characters. There are fewer than ten thousand distinct words in my 3,000,000 rows. > The vector was filled using the description fields values. I ran a vacuum full > analyze before executing any of my queries. > > Here are a couple of tests I performed using the tsearch index and like; > > search_test=# select count(*) from product where vector @@ to_tsquery('oil'); > count > -------- > 226357 > (1 row) > > Time: 191056.230 ms > > search_test=# select count(*) from product where vector @@ to_tsquery('hydrogen'); > count > -------- > 226868 > (1 row) > > Time: 306411.957 ms > > search_test=# select count(*) from product where description like '% oil %'; > count > -------- > 226357 > (1 row) > > Time: 38426.851 ms > > search_test=# select count(*) from product where description like '% hydrogen %'; > count > -------- > 226868 > (1 row) > > Time: 38265.421 ms > > > Both of the likes are using a sequential scan and both of the tsearch queries use > the gist index. Did I miss a configuration parameter, are these queries incorrectly > using tsearch2,or is this tsearch2's average performance? Thanks in advance for the > input! > > Kris > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
