Horton

Re: [HACKERS] Parallel tuplesort (for parallel B-Tree index creation)

Peter Geoghegan <pg@bowt.ie>

View thread View on postgr.es
From: Peter Geoghegan <pg@bowt.ie>
To: Rushabh Lathia <rushabh.lathia@gmail.com>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>, Robert Haas <robertmhaas@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>, Pg Hackers <pgsql-hackers@postgresql.org>, Corey Huinker <corey.huinker@gmail.com>
Date: 2018-01-10T03:12:38Z
Lists: pgsql-hackers 
On Mon, Jan 8, 2018 at 9:44 PM, Rushabh Lathia <rushabh.lathia@gmail.com> wrote:
> I gone through the changes and perform the basic testing. Changes
> looks good and haven't found any unusual during testing

Then I'll mark the patch "Ready for Committer" now. I think that we've
done just about all we can with it.

There is one lingering concern that I cannot shake, which stems from
the fact that the cost model (plan_create_index_workers()) follows the
same generic logic for adding workers as parallel sequential scan, per
Robert's feedback from around March of last year (that is, we more or
less just reuse compute_parallel_worker()). My specific concern is
that this approach may be too aggressive in situations where a
parallel external sort ends up being used instead of a serial internal
sort. No weight is given to any extra temp file costs; a serial
external sort is, in a sense, the baseline, including in cases where
the table is very small and an external sort can actually easily be
avoided iff we do a serial sort.

This is probably not worth doing anything about. The distinction
between internal and external sorts became rather blurred in 9.6 and
10, which, in a way, this patch builds on. If what I describe is a
problem at all, it will very probably only be a problem on small
CREATE INDEX operations, where linear sequential I/O costs are not
already dwarfed by the linearithmic CPU costs. (The dominance of
CPU/comparison costs on larger sorts is the main reason why external
sorts can be faster than internal sorts -- this happens fairly
frequently these days, especially with CREATE INDEX, where being able
to write out the index as it merges on-the-fly helps a lot.)

-- 
Peter Geoghegan

Commits

  1. Support parallel btree index builds.

  2. Report an ERROR if a parallel worker fails to start properly.

  3. Transfer state pertaining to pending REINDEX operations to workers.

  4. Add a barrier primitive for synchronizing backends.

  5. Allow DML commands that create tables to use parallel query.

  6. Refactor GetOldestXmin() to use flags

  7. Fix regression in parallel planning against inheritance tables.

  8. Don't create "holes" in BufFiles, in the new logtape code.

  9. Simplify the code for logical tape read buffers.

  10. Fix excessive memory consumption in the new sort pre-reading code.

  11. Implement binary heap replace-top operation in a smarter way.

  12. Cosmetic code cleanup in commands/extension.c.

  13. Speed up planner's scanning for parallel-query hazards.

  14. Read from the same worker repeatedly until it returns no tuple.

  15. Improve tuplesort.c to support variable merge order. The original coding