On 07/14/2018 12:10 AM, Peter Geoghegan wrote: > On Fri, Jul 13, 2018 at 3:04 PM, Kefan Yang <starord...@gmail.com> wrote: >> 1. Slow on CREATE INDEX cases. >> >> I am still trying to figure out where the bottleneck is. Is the data pattern >> in index creation very different from other cases? Also, pg_qsort has >> 10%-20% advantage at creating index even on sorted data (faster CPU, N = >> 1000000). This is very strange to me since the two sorting routines execute >> exactly the same code when the input data is sorted. > > Yes. CREATE INDEX uses heap TID as a tie-breaker, so it's impossible > for any two index tuples to compare as equal within tuplesort.c, even > though they may be equal in other contexts. This is likely to defeat > things like the Bentley-McIlroy optimization where equal keys are > swapped, which is very effective in the event of many equal keys. > > (Could also be parallelism, though I suppose you probably accounted for that.) >
Hmmm. Those scripts are older than max_parallel_maintenance_workers, so were only setting the regular max_parallel_workers_per_gather GUCs. OTOH these tests were done on fairly small data sets, starting from 10k rows and the 10-20% regression is clearly visible for all scales (we don't use parallel CREATE INDEX for tiny tables, right?). And it's not visible on the i5 CPU at all, which would be a bit strange if it's parallelism-related. So I doubt it's this, but I've tweaked the scripts to also set this GUC and restarted the tests on both machines. Let's see what that does. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
