Re: Parallel Index Scans

On Thu, Feb 9, 2017 at 12:08 PM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> On Wed, Feb 8, 2017 at 10:33 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> I had some offlist discussion with Robert about the above point and we
>> feel that keeping only heap pages for parallel computation might not
>> be future proof as for parallel index only scans there might not be
>> any heap pages. So, it is better to use separate GUC for parallel
>> index (only) scans. We can have two guc's
>> min_parallel_table_scan_size (8MB) and min_parallel_index_scan_size
>> (512kB) for computing parallel scans. The parallel sequential scan
>> and parallel bitmap heap scans can use min_parallel_table_scan_size as
>> a threshold to compute parallel workers as we are doing now. For
>> parallel index scans, both min_parallel_table_scan_size and
>> min_parallel_index_scan_size can be used for threshold; We can
>> compute parallel workers both based on heap_pages to be scanned and
>> index_pages to be scanned and then keep the minimum of those. This
>> will help us to engage parallel index scans when the index pages are
>> lower than threshold but there are many heap pages to be scanned and
>> will also allow keeping a maximum cap on the number of workers based
>> on index scan size.
>
> What about parallel CREATE INDEX? The patch currently uses
> min_parallel_relation_size as an input into the optimizer's custom
> cost model. I had wondered if that made sense. Note that another such
> input is the projected size of the final index.
>

If projected index size is available, then I think Create Index can
also use a somewhat similar formula where we cap the maximum number of
workers based on the size of the index. Now, I am not sure if the
threshold values of guc's kept for the scan are realistic for Create
Index operation.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com