Re: Abbreviated keys for text cost model fix

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