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
