Functional index is 5 times slower than the basic one

Поиск
Список
Период
Сортировка
От jobapply
Тема Functional index is 5 times slower than the basic one
Дата
Msg-id 20050713223509.D720952C2C@svr1.postgresql.org
обсуждение исходный текст
Список pgsql-performance
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


В списке pgsql-performance по дате отправления:

Предыдущее
От: "jobapply"
Дата:
Сообщение: Indexing Function called on VACUUM and sorting ?
Следующее
От: Karim Nassar
Дата:
Сообщение: What's a lot of connections?