The question appeared because of strange issues with functional indexes.
It seems they are recalculated even where it is obviously not needed.

\d+ test:

 i              | integer |                          |
 t              | text    |                          |
 x              | text    |                          |
    "i_i" btree (i)
    "x_i" btree (xpath_string(x, 'data'::text))
    "x_ii" btree (xpath_string(x, 'movie/characters/character'::text))
    "x_iii" btree (xpath_string(x, 'movie/rating'::text))

When I run

After text

INFO:  analyzing "public.test"
INFO:  "test": scanned 733 of 733 pages, containing 10000 live rows and 0
dead rows; 3000 rows in sample, 10000 estimated total rows

a lot of xpath_string calls occur. 
Does VACUUM rebuild indexes ? What for to recalculate that all?
It makes VACUUMing very slow.

Simple VACUUM call does not lead to such function calls.

When I do 
select * from test order by xpath_string(x, 'movie/rating'::text) limit 1000
offset 10;

Planner uses index x_iii (as it should, ok here):       Limit -> Index scan.
But many of calls to xpath_string occur in execution time. 
Why ? Index is calculated already and everything is so immutable..

Please answer if you have any ideas.. Functional indexes seemed so great
first, but now I uncover weird issues I can't understand..

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to