I've the usual 600K-800K record table a bunch of records (one coming from another table) are glued to form a tsvector, 3 with the same weight, the others with different weight. There is a clustered index on the pk. (is it worth to keep it?) I wrote a pretty long function that build up a tsquery from user input. I tested the function to see how it performed. Most of the queries were under 200ms. I spent some time writing another function that just build up another tsquery. Tested the second and times where in the range of 1600-2000ms. I thought it was a problem of the second function... but the first started to have execution time in the same 1600-2000ms. I thought it was cache and I started to run the same query some times but execution times continued to be in the range of 1600-2000ms. I decided to vacuum full. Vacuum full wait forever when it is taking care of the indexed table. CPU 0.12s/0.42u sec elapsed 2.31 sec.INFO: index "catalog_items_ft1idx_index" now contains 833496 row versions in 41089 pages DETAIL: 7792699 index row versions were removed. 3029 index pages have been deleted, 3029 are currently reusable. CPU 1.83s/9.26u sec elapsed 61.89 sec. And then it goes no further. I've been able to vacuum full dropping the gin index and then vacuum and vacuum full... but it is still very very slow. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
