On 07/14/2018 12:10 AM, Peter Geoghegan wrote:
> On Fri, Jul 13, 2018 at 3:04 PM, Kefan Yang <starordust@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
