[PERFORM] Indexing Function called on VACUUM and sorting ?
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)) 1) When I run VACUUM FULL ANALYZE VERBOSE OR VACUUM ANALYZE After text INFO: analyzing public.test INFO: test: scanned 733 of 733 pages, containing 1 live rows and 0 dead rows; 3000 rows in sample, 1 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. 2) 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
[PERFORM] Functional index is 5 times slower than the basic one
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 +--+---+- i | integer | | t | text | | x | text | | d | double precision | | Indexes: floatind btree (d) i_i btree (i) CLUSTER t_ind btree (t) t_x_ind btree (t, xpath_string(x, 'data'::text)) 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)) Has OIDs: no explain analyze select count(*) from ( select * from test order by xpath_string(x, 'movie/rating'::text) limit 1000 offset 10 ) a; QUERY PLAN Aggregate (cost=342.37..342.37 rows=1 width=0) (actual time=403.580..403.584 rows=1 loops=1) - Subquery Scan a (cost=3.27..339.87 rows=1000 width=0) (actual time=4.252..398.261 rows=1000 loops=1) - Limit (cost=3.27..329.87 rows=1000 width=969) (actual time=4.242..389.557 rows=1000 loops=1) - Index Scan using x_iii on test (cost=0.00..3266.00 rows=1 width=969) (actual time=0.488..381.049 rows=1010 loops=1) Total runtime: 403.695 ms explain analyze select count(*) from ( select * from test order by t limit 1000 offset 10 ) a; QUERY PLAN Aggregate (cost=339.84..339.84 rows=1 width=0) (actual time=26.662..26.666 rows=1 loops=1) - Subquery Scan a (cost=3.24..337.34 rows=1000 width=0) (actual time=0.228..22.416 rows=1000 loops=1) - Limit (cost=3.24..327.34 rows=1000 width=969) (actual time=0.217..14.244 rows=1000 loops=1) - Index Scan using t_ind on test (cost=0.00..3241.00 rows=1 width=969) (actual time=0.099..6.371 rows=1010 loops=1) Total runtime: 26.749 ms ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Indexing Function called on VACUUM and sorting ?
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 (xpath_string(x, 'movie/rating'::text)) 1) When I run VACUUM FULL ANALYZE VERBOSE OR VACUUM ANALYZE 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. 2) 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Sorting on longer key is faster ?
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 i20 ORDER BY x || t; QUERY PLAN -- Sort (cost=2282.65..2284.92 rows=907 width=946) (actual time=74.982..79.114 rows=950 loops=1) Sort Key: (x || t) - Index Scan using i_i on test (cost=0.00..2238.09 rows=907 width=946) (actual time=0.077..51.015 rows=950 loops=1) Index Cond: (i 20) Total runtime: 85.944 ms (5 rows) phoeniks= explain analyze SELECT * FROM test WHERE i20 ORDER BY x; QUERY PLAN - Sort (cost=2280.38..2282.65 rows=907 width=946) (actual time=175.431..179.239 rows=950 loops=1) Sort Key: x - Index Scan using i_i on test (cost=0.00..2235.82 rows=907 width=946) (actual time=0.024..5.378 rows=950 loops=1) Index Cond: (i 20) Total runtime: 183.317 ms (5 rows) phoeniks= \d+ test Table public.test Column | Type | Modifiers | Description +-+---+- i | integer | | t | text| | x | text| | Indexes: i_i btree (i) x_i btree (xpath_string(x, 'data'::text)) x_ii btree (xpath_string(x, 'movie/characters/character'::text)) Has OIDs: no ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
