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 i<20 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 i<20 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


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

Предыдущее
От: "Dario Pudlo"
Дата:
Сообщение: join and query planner
Следующее
От: "Alexander Kirpa"
Дата:
Сообщение: Re: Data Warehousing Tuning