Horton

merge_sort.sql

application/sql

Download
Filename: merge_sort.sql
Type: application/sql
Part: 0
Message: Re: MergeAppend could consider sorting cheapest child path 
SET work_mem = '64MB';

EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, BUFFERS ON, SUMMARY ON)
SELECT random() AS x FROM generate_series(1,1E5)
  UNION ALL
SELECT random() AS x FROM generate_series(1,1E5)
  UNION ALL
SELECT random() AS x FROM generate_series(1,1E5)
  UNION ALL
SELECT random() AS x FROM generate_series(1,1E5)
  UNION ALL
SELECT random() AS x FROM generate_series(1,1E5)
  UNION ALL
SELECT random() AS x FROM generate_series(1,1E5)
  UNION ALL
SELECT random() AS x FROM generate_series(1,1E5)
  UNION ALL
SELECT random() AS x FROM generate_series(1,1E5)
  UNION ALL
SELECT random() AS x FROM generate_series(1,1E5)
  UNION ALL
SELECT random() AS x FROM generate_series(1,1E5)
ORDER BY x;

EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, BUFFERS ON, SUMMARY ON)
(SELECT random() AS x FROM generate_series(1,1E5) ORDER BY x)
  UNION ALL
(SELECT random() AS x FROM generate_series(1,1E5) ORDER BY x)
  UNION ALL
(SELECT random() AS x FROM generate_series(1,1E5) ORDER BY x)
  UNION ALL
(SELECT random() AS x FROM generate_series(1,1E5) ORDER BY x)
  UNION ALL
(SELECT random() AS x FROM generate_series(1,1E5) ORDER BY x)
  UNION ALL
(SELECT random() AS x FROM generate_series(1,1E5) ORDER BY x)
  UNION ALL
(SELECT random() AS x FROM generate_series(1,1E5) ORDER BY x)
  UNION ALL
(SELECT random() AS x FROM generate_series(1,1E5) ORDER BY x)
  UNION ALL
(SELECT random() AS x FROM generate_series(1,1E5) ORDER BY x)
  UNION ALL
(SELECT random() AS x FROM generate_series(1,1E5) ORDER BY x)
ORDER BY x;

/*
 Sort  (cost=117157.87..119657.87 rows=1000000 width=8) (actual rows=1000000.00 loops=1)
   Sort Key: (random())
   Sort Method: quicksort  Memory: 24577kB
   ->  Append  (cost=0.00..17500.03 rows=1000000 width=8) (actual rows=1000000.00 loops=1)
         ->  Function Scan on generate_series  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
         ->  Function Scan on generate_series generate_series_1  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
         ->  Function Scan on generate_series generate_series_2  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
         ->  Function Scan on generate_series generate_series_3  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
         ->  Function Scan on generate_series generate_series_4  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
         ->  Function Scan on generate_series generate_series_5  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
         ->  Function Scan on generate_series generate_series_6  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
         ->  Function Scan on generate_series generate_series_7  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
         ->  Function Scan on generate_series generate_series_8  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
         ->  Function Scan on generate_series generate_series_9  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)

Merge Append  (cost=95548.39..119658.03 rows=1000000 width=8) (actual rows=1000000.00 loops=1)
   Sort Key: (random())
   ->  Sort  (cost=9554.82..9804.82 rows=100000 width=8) (actual rows=100000.00 loops=1)
         Sort Key: (random())
         Sort Method: quicksort  Memory: 3073kB
         ->  Function Scan on generate_series  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
   ->  Sort  (cost=9554.82..9804.82 rows=100000 width=8) (actual rows=100000.00 loops=1)
         Sort Key: (random())
         Sort Method: quicksort  Memory: 3073kB
         ->  Function Scan on generate_series generate_series_1  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
   ->  Sort  (cost=9554.82..9804.82 rows=100000 width=8) (actual rows=100000.00 loops=1)
         Sort Key: (random())
         Sort Method: quicksort  Memory: 3073kB
         ->  Function Scan on generate_series generate_series_2  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
   ->  Sort  (cost=9554.82..9804.82 rows=100000 width=8) (actual rows=100000.00 loops=1)
         Sort Key: (random())
         Sort Method: quicksort  Memory: 3073kB
         ->  Function Scan on generate_series generate_series_3  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
   ->  Sort  (cost=9554.82..9804.82 rows=100000 width=8) (actual rows=100000.00 loops=1)
         Sort Key: (random())
         Sort Method: quicksort  Memory: 3073kB
         ->  Function Scan on generate_series generate_series_4  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
   ->  Sort  (cost=9554.82..9804.82 rows=100000 width=8) (actual rows=100000.00 loops=1)
         Sort Key: (random())
         Sort Method: quicksort  Memory: 3073kB
         ->  Function Scan on generate_series generate_series_5  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
   ->  Sort  (cost=9554.82..9804.82 rows=100000 width=8) (actual rows=100000.00 loops=1)
         Sort Key: (random())
         Sort Method: quicksort  Memory: 3073kB
         ->  Function Scan on generate_series generate_series_6  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
   ->  Sort  (cost=9554.82..9804.82 rows=100000 width=8) (actual rows=100000.00 loops=1)
         Sort Key: (random())
         Sort Method: quicksort  Memory: 3073kB
         ->  Function Scan on generate_series generate_series_7  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
   ->  Sort  (cost=9554.82..9804.82 rows=100000 width=8) (actual rows=100000.00 loops=1)
         Sort Key: (random())
         Sort Method: quicksort  Memory: 3073kB
         ->  Function Scan on generate_series generate_series_8  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
   ->  Sort  (cost=9554.82..9804.82 rows=100000 width=8) (actual rows=100000.00 loops=1)
         Sort Key: (random())
         Sort Method: quicksort  Memory: 3073kB
         ->  Function Scan on generate_series generate_series_9  (cost=0.00..1250.00 rows=100000 width=8) (actual rows=100000.00 loops=1)
*/