Hm, ok, setting up a new database with the Conway implementation I see the
time penalty is not "negligible". However it is quite tolerable:
test=> explain analyze select * from test order by a;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Sort (cost=69.83..72.33 rows=1000 width=32) (actual time=772.175..900.155
rows=206300 loops=1)
Sort Key: a
-> Seq Scan on test (cost=0.00..20.00 rows=1000 width=32) (actual
time=0.009..203.362 rows=206300 loops=1)
Total runtime: 1055.137 ms
(4 rows)
test=> explain analyze select * from test order by lower(a);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Sort (cost=72.33..74.83 rows=1000 width=32) (actual time=1190.248..1318.607
rows=206300 loops=1)
Sort Key: lower(a)
-> Seq Scan on test (cost=0.00..22.50 rows=1000 width=32) (actual
time=0.021..517.679 rows=206300 loops=1)
Total runtime: 1565.564 ms
(4 rows)
test=> explain analyze select * from test order by strxfrm(a,'en_US');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Sort (cost=72.33..74.83 rows=1000 width=32) (actual time=2135.535..2263.429
rows=206300 loops=1)
Sort Key: strxfrm(a, 'en_US'::text)
-> Seq Scan on test (cost=0.00..22.50 rows=1000 width=32) (actual
time=0.060..1384.428 rows=206300 loops=1)
Total runtime: 2516.981 ms
(4 rows)
test=> explain analyze select * from test order by strxfrm(a,'fr_CA');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Sort (cost=72.33..74.83 rows=1000 width=32) (actual time=2155.991..2284.054
rows=206300 loops=1)
Sort Key: strxfrm(a, 'fr_CA'::text)
-> Seq Scan on test (cost=0.00..22.50 rows=1000 width=32) (actual
time=18.455..1403.390 rows=206300 loops=1)
Total runtime: 2538.886 ms
(4 rows)
--
greg
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
