Re: FETCH FIRST clause PERCENT option - Mailing list pgsql-hackers

Hello.

At Wed, 10 Jul 2019 15:02:57 +0900 (Tokyo Standard Time), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in
<20190710.150257.260806103.horikyota.ntt@gmail.com>
> It is seen by a simpler test.
> 
> create table t as select a from generate_series(0, 99999) a;
> analyze t;
> explain analyze select * from t order by a desc;
>  Execution Time: 116.613 ms
> explain analyze select * from t order by a desc fetch first 1 percent rows only;
>  Execution Time: 158.458 ms
> explain analyze select * from t order by a desc fetch first 100 percent rows only;
>  Execution Time: 364.442 ms
> 
> I didn't looked closer to the version. Fetching from tuplestore
> and returning all tuples costs 206ms and it is exceeding the cost
> of fething of the whole table and returning all tuples. I don't
> believe tuplestore that isn't splling out to disk is so slower
> than (cached) table access.
> 
> Other than that, we can rip the clause if it is 100%

As a more significant point, I found that the first query in the
aboves runs faster by about 10-18% on master(unpatched).

explain analyze select * from t order by a desc;
 Execution Time: 96.690 ms

But perf didn't give me useful information.

patched:

11857    11.7065  postgres                 qsort_ssup
9026      8.9114  postgres                 ApplySortComparator
6443      6.3612  [vdso] (tgid:8388 range:0x7ffed49ed000-0x7ffed49eefff) [vdso] (tgid:8388
range:0x7ffed49ed000-0x7ffed49eefff)
5826      5.7520  postgres                 btint4fastcmp
4699      4.6393  no-vmlinux               /no-vmlinux
3451      3.4072  libc-2.17.so             __memcpy_ssse3_back
3270      3.2285  postgres                 LogicalTapeWrite
2972      2.9343  postgres                 copytup_heap
2961      2.9234  postgres                 readtup_heap
2769      2.7338  postgres                 LogicalTapeRead
2457      2.4258  postgres                 GetMemoryChunkContext
2147      2.1197  postgres                 InstrStopNode
2021      1.9953  postgres                 heapgettup_pagemode
1583      1.5629  postgres                 writetup_heap
1555      1.5353  postgres                 tuplesort_gettuple_common
1508      1.4889  postgres                 AllocSetAlloc
...

master:

12932    12.0168  postgres                 qsort_ssup
9491      8.8193  postgres                 ApplySortComparator
6705      6.2305  postgres                 btint4fastcmp
6557      6.0930  [vdso] (tgid:6341 range:0x7ffdd0315000-0x7ffdd0316fff) [vdso] (tgid:6341
range:0x7ffdd0315000-0x7ffdd0316fff)
4874      4.5291  no-vmlinux               /no-vmlinux
4059      3.7717  postgres                 readtup_heap
3707      3.4447  libc-2.17.so             __memcpy_ssse3_back
3583      3.3294  postgres                 LogicalTapeWrite
3382      3.1427  postgres                 LogicalTapeRead
3001      2.7886  postgres                 copytup_heap
2522      2.3435  postgres                 GetMemoryChunkContext
2464      2.2896  postgres                 heapgettup_pagemode
2115      1.9653  postgres                 InstrStopNode
1847      1.7163  postgres                 tuplesort_gettuple_common
1652      1.5351  postgres                 writetup_heap
1565      1.4542  postgres                 AllocSetAlloc

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center