Message-ID: <42D303CA.2020808@arbash-meinel.com>
Date: Mon, 11 Jul 2005 18:42:02 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Macintosh/20050317)
MIME-Version: 1.0
To: jobapply <jobapply@nextmail.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sorting on longer key is faster ?
References: <20050709183842.2EABF52820@svr1.postgresql.org>
In-Reply-To: <20050709183842.2EABF52820@svr1.postgresql.org>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigB810D5620F6C038341525139"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigB810D5620F6C038341525139
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

jobapply wrote:
> 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
>

What types are x and t, I have the feeling "x || t" is actually a
boolean, so it is only a True/False sort, while ORDER BY x has to do
some sort of string comparison (which might actually be a locale
depended comparison, and strcoll can be very slow on some locales)

John
=:->

> ----------------------------------------------------------------------------
> ----------------------------------------------
>  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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>


--------------enigB810D5620F6C038341525139
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD4DBQFC0wPKJdeBCYSNAAMRAk14AJdsYOPYY3PHuBNilislLLiqOX11AKCC4Cki
S4ZzdD/Y9WtZEsc0LA+xwA==
=GwKr
-----END PGP SIGNATURE-----

--------------enigB810D5620F6C038341525139--