Re: FETCH FIRST clause PERCENT option

Hello.

At Wed, 10 Jul 2019 15:02:57 +0900 (Tokyo Standard Time), Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> wrote in <20190710(dot)150257(dot)260806103(dot)horikyota(dot)ntt(at)gmail(dot)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