X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 910A047580B for ; Mon, 14 Apr 2003 20:49:13 -0400 (EDT) Received: from workskillsprofessionals.com.au (adsl-202-72-167-48.prem.westnet.com.au [202.72.167.48]) by postgresql.org (Postfix) with ESMTP id 0299C474E44 for ; Mon, 14 Apr 2003 20:49:12 -0400 (EDT) Received: from microvac [192.168.1.124] by workskillsprofessionals.com.au [192.168.1.188] with SMTP (MDaemon.PRO.v4.0.5.R) for ; Tue, 15 Apr 2003 08:56:51 +0800 Message-ID: <002e01c302e8$db771ab0$7c01a8c0@microvac> From: "Sigi Jekabsons" To: Subject: fast case-insensitive sort Date: Tue, 15 Apr 2003 08:49:23 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-MDRemoteIP: 192.168.1.124 X-Return-Path: sigi.j@workskillsprofessionals.com.au X-MDaemon-Deliver-To: pgsql-sql@postgresql.org X-Spam-Status: No, hits=-1.6 required=5.0 tests=BAYES_30 version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/215 X-Sequence-Number: 12916 I'm having trouble getting postgres to use an index when doing an ORDER BY UPPER(surname), for instance. I can create an index on UPPER(surname), but it doesn't use it in the query - is there a better way of doing a fast case insensitive sort? This is the query plan of ORDER BY surname, which uses the index I've created on surname: asp_employ=# EXPLAIN ANALYSE SELECT * FROM cands ORDER BY surname; QUERY PLAN ---------------------------------------------------------------------------- ----------------------------------------------------------- Index Scan using cands_surname_idx on cands (cost=0.00..3983.55 rows=31348 width=1971) (actual time=0.27..647.09 rows=31348 loops=1) Total runtime: 664.62 msec (2 rows) Note the time taken. This is the query plan of sorting by UPPER(surname) before and after creating the index: asp_employ=# EXPLAIN ANALYSE SELECT * FROM cands ORDER BY UPPER(surname); QUERY PLAN ---------------------------------------------------------------------------- --------------------------------------- Sort (cost=39701.56..39779.93 rows=31348 width=1971) (actual time=3384.08..3444.77 rows=31348 loops=1) Sort Key: upper((surname)::text) -> Seq Scan on cands (cost=0.00..1165.48 rows=31348 width=1971) (actual time=0.06..628.09 rows=31348 loops=1) Total runtime: 3875.90 msec (4 rows) asp_employ=# CREATE INDEX cand_sur_up_idx ON cands (UPPER(surname)); CREATE INDEX asp_employ=# EXPLAIN ANALYSE SELECT * FROM cands ORDER BY UPPER(surname); QUERY PLAN ---------------------------------------------------------------------------- --------------------------------------- Sort (cost=39701.56..39779.93 rows=31348 width=1971) (actual time=3368.43..3429.51 rows=31348 loops=1) Sort Key: upper((surname)::text) -> Seq Scan on cands (cost=0.00..1165.48 rows=31348 width=1971) (actual time=0.07..621.78 rows=31348 loops=1) Total runtime: 3859.33 msec (4 rows) The Seq Scan is much slower. It may be interesting to note that this uses the index: asp_employ=# explain analyse select * from cands where upper(surname)='SMITH'; QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------- Index Scan using cand_sur_up_idx on cands (cost=0.00..578.66 rows=157 width=1972) (actual time=54.52..60.06 rows=224 loops=1) Index Cond: (upper((surname)::text) = 'SMITH'::text) Total runtime: 60.36 msec (3 rows) I created a thread on this topic on the Ars Technica forums, but there was not a solution - the discussion for reference only is here: http://arstechnica.infopop.net/OpenTopic/page?a=tpc&s=50009562&f=6330927813& m=1070953065 So should I be using a specific type of index, or is there a better way?