Hello. At Tue, 9 Jul 2019 21:56:32 -0600, Ryan Lambert <r...@rustprooflabs.com> wrote in <CAN-V+g-rwFp=xqejowbjuggnlegmi1qdhajt3h1eqm16yqw...@mail.gmail.com> > I did some more testing. I initialized a database with 1 million rows with > indexes and joins to test against and ran pgbench with a few different > settings for % to return. I started with a base query not utilizing the > new functionality. The queries used are similar to my prior examples, code > at [1]. > > createdb bench_test > psql -d bench_test -f init/reporting.sql -v scale=10 > > The following provided 3.21 TPS and an average latency of 623. The > "per_change_" columns in the table below use those values. > > pgbench -c 2 -j 2 -T 600 -P 60 -s 10 \ > -f tests/reporting1.sql bench_test > > The remainder of the tests use the following, only adjusting fetch_percent > value: > > pgbench -c 2 -j 2 -T 600 -P 60 -s 10 \ > --define=fetch_percent=1 \ > -f tests/reporting_fetch_percent.sql \ > bench_test > > > Returning 1% it runs well. By 10% the TPS drops by 30% while the average > latency increases by 43%. When returning 95% of the table latency has > increased by 548%. > > > fetch_percent | tps | latency_avg_ms | per_change_tps | per_change_latency > ---------------+------+----------------+----------------+-------------------- > 1 | 3.37 | 593 | 0.05 | -0.05 > 5 | 2.85 | 700 | -0.11 | 0.12 > 10 | 2.24 | 891 | -0.30 | 0.43 > 25 | 1.40 | 1423 | -0.56 | 1.28 > 45 | 0.93 | 2147 | -0.71 | 2.45 > 95 | 0.49 | 4035 | -0.85 | 5.48 > > > I manually tested the inner select queries without the outer aggregation > thinking it might be a different story with a simple select and no CTE. > Unfortunately it showed the same overall characteristics. 1% returns in > about 550 ms, 45% took 1950, and 95% took 4050. > > [1] https://github.com/rustprooflabs/pgbench-tests
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% regards. -- Kyotaro Horiguchi NTT Open Source Software Center
