Re: Abbreviated keys for text cost model fix - Mailing list pgsql-hackers

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 msExecution time:
25144.538ms
 
(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 msExecution 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 msExecution 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 msExecution time:
25532.947ms
 
(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
internalmerge  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 msExecution time: 1195.891
ms
(7 rows)

Time: 1196.514 ms
test=#


-- 
Cheers, Jeremy