On 23/02/15 16:40, Tomas Vondra wrote: > On 22.2.2015 22:30, Peter Geoghegan wrote: >> You should try it with the data fully sorted like this, but with one >> tiny difference: The very last tuple is out of order. How does that >> look?
If this case is actually important, a merge-sort can take significant advantage of the partial order: test=# explain analyze select * from (select * from stuff_text_asc order by randtxt offset 100000000000) foo; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=247054.81..247054.81 rows=1 width=18) (actual time=25133.029..25133.029 rows=0 loops=1) -> Sort (cost=242054.81..247054.81 rows=2000001 width=18) (actual time=25025.931..25088.406 rows=2000001 loops=1) Sort Key: stuff_text_asc.randtxt Sort Method: quicksort Memory: 221213kB Compares: 95541376 -> Seq Scan on stuff_text_asc (cost=0.00..32739.01 rows=2000001 width=18) (actual time=0.011..118.390 rows=2000001 loops=1) Planning time: 0.080 ms Execution time: 25144.538 ms (7 rows) Time: 25145.185 ms test=# test=# test=# set enable_intmerge_sort to on; SET Time: 0.378 ms test=# explain analyze select * from (select * from stuff_text_asc order by randtxt offset 100000000000) foo; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=247054.81..247054.81 rows=1 width=18) (actual time=1051.603..1051.603 rows=0 loops=1) -> Sort (cost=242054.81..247054.81 rows=2000001 width=18) (actual time=943.304..1006.988 rows=2000001 loops=1) Sort Key: stuff_text_asc.randtxt Sort Method: internal merge Memory: 221213kB Compares: 2000002 -> Seq Scan on stuff_text_asc (cost=0.00..32739.01 rows=2000001 width=18) (actual time=0.009..98.474 rows=2000001 loops=1) Planning time: 0.072 ms Execution time: 1063.434 ms (7 rows) Time: 1064.113 ms test=# test=# set enable_intmerge_sort to off; SET Time: 0.353 ms test=# test=# test=# test=# test=# test=# explain analyze select count(distinct randtxt) from stuff_text_asc; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=37739.01..37739.02 rows=1 width=18) (actual time=25196.814..25196.815 rows=1 loops=1) -> Seq Scan on stuff_text_asc (cost=0.00..32739.01 rows=2000001 width=18) (actual time=0.010..114.995 rows=2000001 loops=1) Planning time: 0.053 ms Execution time: 25196.857 ms (4 rows) Time: 25197.371 ms test=# test=# explain analyze select count(*) from (select distinct randtxt from stuff_text_asc) as foo; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=277054.83..277054.84 rows=1 width=0) (actual time=25521.258..25521.258 rows=1 loops=1) -> Unique (cost=242054.81..252054.81 rows=2000001 width=18) (actual time=25101.157..25438.622 rows=1999100 loops=1) -> Sort (cost=242054.81..247054.81 rows=2000001 width=18) (actual time=25101.156..25184.436 rows=2000001 loops=1) Sort Key: stuff_text_asc.randtxt Sort Method: quicksort Memory: 221213kB Compares: 95541376 -> Seq Scan on stuff_text_asc (cost=0.00..32739.01 rows=2000001 width=18) (actual time=0.011..116.509 rows=2000001 loops=1) Planning time: 0.088 ms Execution time: 25532.947 ms (8 rows) Time: 25533.642 ms test=# test=# test=# set enable_intmerge_sort to on; SET Time: 0.401 ms test=# explain analyze select count(*) from (select distinct randtxt from stuff_text_asc) as foo; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=272054.82..272054.83 rows=1 width=0) (actual time=1184.289..1184.289 rows=1 loops=1) -> Sort (cost=242054.81..247054.81 rows=2000001 width=18) (actual time=1037.019..1100.720 rows=1999100 loops=1) Sort Key: stuff_text_asc.randtxt Sort Method: dedup internal merge Memory: 221143kB Compares: 2000001 -> Seq Scan on stuff_text_asc (cost=0.00..32739.01 rows=2000001 width=18) (actual time=0.010..106.729 rows=2000001 loops=1) Planning time: 0.086 ms Execution time: 1195.891 ms (7 rows) Time: 1196.514 ms test=# -- Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers