X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 323135282C for ; Sat, 9 Jul 2005 15:38:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61806-02 for ; Sat, 9 Jul 2005 18:38:47 +0000 (GMT) Received: from nextmail.ru (unknown [83.222.5.149]) by svr1.postgresql.org (Postfix) with SMTP id 2EABF52820 for ; Sat, 9 Jul 2005 15:38:42 -0300 (ADT) Received: (qmail 57103 invoked from network); 9 Jul 2005 18:38:51 -0000 Received: from ppp85-140-125-53.pppoe.mtu-net.ru (HELO z) (85.140.125.53) by nextmail.ru with SMTP; 9 Jul 2005 18:38:51 -0000 From: "jobapply" To: Subject: Sorting on longer key is faster ? Date: Sat, 9 Jul 2005 22:38:40 +0400 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 Thread-Index: AcWEtWWnpTyLkFRvR2eGsKrTi7ZHLA== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-Id: <20050709183842.2EABF52820@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.078 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, MSGID_FROM_MTA_ID X-Spam-Level: ** X-Archive-Number: 200507/112 X-Sequence-Number: 13353 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