Thread: Sorting on longer key is faster ?
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
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 >
Attachment
"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
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.
Attachment
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 =:->