From: | "jobapply" <jobapply(at)nextmail(dot)ru> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Sorting on longer key is faster ? |
Date: | 2005-07-09 18:38:40 |
Message-ID: | 20050709183842.2EABF52820@svr1.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alex Stapleton | 2005-07-09 21:48:43 | Re: Mount database on RAM disk? |
Previous Message | Michael Fuhr | 2005-07-09 00:58:15 | Re: How to revoke a password |