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 >
signature.asc
Description: OpenPGP digital signature
