Re: CustomScan under the Gather node? - Mailing list pgsql-hackers

> On Thu, Jan 28, 2016 at 10:50 AM, Kouhei Kaigai <kaigai@ak.jp.nec.com> wrote:
> >> If I would make a proof-of-concept patch with interface itself, it
> >> seems to me file_fdw may be a good candidate for this enhancement.
> >> It is not a field for postgres_fdw.
> >>
> > The attached patch is enhancement of FDW/CSP interface and PoC feature
> > of file_fdw to scan source file partially. It was smaller enhancement
> > than my expectations.
> >
> > It works as follows. This query tried to read 20M rows from a CSV file,
> > using 3 background worker processes.
> >
> > postgres=# set max_parallel_degree = 3;
> > SET
> > postgres=# explain analyze select * from test_csv where id % 20 = 6;
> >                                   QUERY PLAN
> >
> ----------------------------------------------------------------------------
> ----
> >  Gather  (cost=1000.00..194108.60 rows=94056 width=52)
> >          (actual time=0.570..19268.010 rows=2000000 loops=1)
> >    Number of Workers: 3
> >    ->  Parallel Foreign Scan on test_csv  (cost=0.00..183703.00 rows=94056
> width=52)
> >                                   (actual time=0.180..12744.655 rows=500000
> loops=4)
> >          Filter: ((id % 20) = 6)
> >          Rows Removed by Filter: 9500000
> >          Foreign File: /tmp/testdata.csv
> >          Foreign File Size: 1504892535
> >  Planning time: 0.147 ms
> >  Execution time: 19330.201 ms
> > (9 rows)
> 
> Could you try it not in parallel and then with 1, 2, 3, and 4 workers
> and post the times for all?
>
The above query has 5% selectivity on the entire CSV file.
Its execution time (total, only ForeignScan) are below

             total         ForeignScan        diff
0 workers: 17584.319 ms   17555.904 ms      28.415 ms
1 workers: 18464.476 ms   18110.968 ms     353.508 ms
2 workers: 19042.755 ms   14580.335 ms    4462.420 ms
3 workers: 19318.254 ms   12668.912 ms    6649.342 ms
4 workers: 21732.910 ms   13596.788 ms    8136.122 ms
5 workers: 23486.846 ms   14533.409 ms    8953.437 ms

This workstation has 4 CPU cores, so it is natural nworkers=3 records the
peak performance on ForeignScan portion. On the other hands, nworkers>1 also
recorded unignorable time consumption (probably, by Gather node?)

An interesting observation was, less selectivity (1% and 0%) didn't change the
result so much. Something consumes CPU time other than file_fdw.

* selectivity 1%
               total       ForeignScan       diff
0 workers: 17573.572 ms   17566.875 ms      6.697 ms
1 workers: 18098.070 ms   18020.790 ms     77.280 ms
2 workers: 18676.078 ms   14600.749 ms   4075.329 ms
3 workers: 18830.597 ms   12731.459 ms   6099.138 ms
4 workers: 21015.842 ms   13590.657 ms   7425.185 ms
5 workers: 22865.496 ms   14634.342 ms   8231.154 ms

* selectivity 0% (...so Gather didn't work hard actually)
              total        ForeignScan       diff
0 workers: 17551.011 ms   17550.811 ms      0.200 ms
1 workers: 18055.185 ms   18048.975 ms      6.210 ms
2 workers: 18567.660 ms   14593.974 ms   3973.686 ms
3 workers: 18649.819 ms   12671.429 ms   5978.390 ms
4 workers: 20619.184 ms   13606.715 ms   7012.469 ms
5 workers: 22557.575 ms   14594.420 ms   7963.155 ms

Further investigation will need....

Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>