pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSorting on longer key is faster ?
5+ messages / 3 participants
[nested] [flat]
* Sorting on longer key is faster ?
@ 2005-07-09 18:38 jobapply <jobapply@nextmail.ru>
0 siblings, 3 replies; 5+ messages in thread
From: jobapply @ 2005-07-09 18:38 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sorting on longer key is faster ?
@ 2005-07-11 23:42 John A Meinel <john@arbash-meinel.com>
parent: jobapply <jobapply@nextmail.ru>
2 siblings, 1 reply; 5+ messages in thread
From: John A Meinel @ 2005-07-11 23:42 UTC (permalink / raw)
To: jobapply <jobapply@nextmail.ru>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sorting on longer key is faster ?
@ 2005-07-11 23:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: jobapply <jobapply@nextmail.ru>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-07-11 23:47 UTC (permalink / raw)
To: jobapply <jobapply@nextmail.ru>; +Cc: pgsql-performance
"jobapply" <jobapply@nextmail.ru> writes:
> The 2 queries are almost same, but ORDER BY x||t is FASTER than ORDER BY x..
> How can that be possible?
Hmm, how long are the x values? Is it possible many of them are
TOASTed?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sorting on longer key is faster ?
@ 2005-07-12 01:48 John A Meinel <john@arbash-meinel.com>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 0 replies; 5+ messages in thread
From: John A Meinel @ 2005-07-12 01:48 UTC (permalink / raw)
To: Chris Travers <chris@metatrontech.com>; pgsql-performance
Chris Travers wrote:
> John A Meinel wrote:
>
>> 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)
>>
>>
>>
> Am I reading this that wrong? I would think that x || t would mean
> "concatenate x and t."
Sorry, I think you are right. I was getting my operators mixed up.
>
> This is interesting. I never through of writing a multicolumn sort this
> way....
I'm also surprised that the sort is faster with a merge operation. Are
you using UNICODE as the database format? I'm just wondering if it is
doing something funny like casting it to an easier to sort type.
>
> Best Wishes,
> Chris Travers
> Metatron Technology Consulting
PS> Don't forget to Reply All so that your messages go back to the list.
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sorting on longer key is faster ?
@ 2005-07-12 01:52 John A Meinel <john@arbash-meinel.com>
parent: jobapply <jobapply@nextmail.ru>
2 siblings, 0 replies; 5+ messages in thread
From: John A Meinel @ 2005-07-12 01:52 UTC (permalink / raw)
To: jobapply <jobapply@nextmail.ru>; +Cc: pgsql-performance
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
I also thought of another possibility. Are there a lot of similar
entries in X? Meaning that the same value is repeated over and over? It
is possible that the sort code has a weakness when sorting equal values.
For instance, if it was doing a Hash aggregation, you would have the
same hash repeated. (It isn't I'm just mentioning a case where it might
affect something).
If it is creating a tree representation, it might cause some sort of
pathological worst-case behavior, where all entries keep adding to the
same side of the tree, rather than being more balanced.
I don't know the internals of postgresql sorting, but just some ideas.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-07-12 01:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-09 18:38 Sorting on longer key is faster ? jobapply <jobapply@nextmail.ru>
2005-07-11 23:42 ` John A Meinel <john@arbash-meinel.com>
2005-07-12 01:48 ` John A Meinel <john@arbash-meinel.com>
2005-07-11 23:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-12 01:52 ` John A Meinel <john@arbash-meinel.com>
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