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=10000
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=10000
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
