It seems functional indexes are recalculated even where it is obviously not
needed.
\d+ test:
i | integer | |
t | text| |
x | text| |
"i_i" btree (i)
"x_iii" btree
VACUUM FULL ANALYZE is performed right before tests.
UPDATE test SET t = xpath_string(x, 'movie/rating'::text); is performed also
to make selects equal.
Xpath_string is IMMUTABLE.
Table "public.test"
Column | Type | Modifiers | Description
+--+
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|
The 2 queries are almost same, but ORDER BY x||t is FASTER than ORDER BY x..
How can that be possible?
Btw: x and x||t are same ordered
phoeniks=> explain analyze SELECT * FROM test WHERE i<20 ORDER BY x || t;
QUERY PLAN
-
