From: John A Meinel <john@arbash-meinel.com>
To: jobapply <jobapply@nextmail.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sorting on longer key is faster ?
Date: Mon, 11 Jul 2005 18:42:02 -0500
Message-ID: <42D303CA.2020808@arbash-meinel.com> (raw)
In-Reply-To: <20050709183842.2EABF52820@svr1.postgresql.org>
References: <20050709183842.2EABF52820@svr1.postgresql.org>
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
>
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, jobapply@nextmail.ru
Subject: Re: Sorting on longer key is faster ?
In-Reply-To: <42D303CA.2020808@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox