Re: Parallel Queries and PostGIS

On Mon, Mar 28, 2016 at 12:18 PM, Paul Ramsey <pramsey(at)cleverelephant(dot)ca> wrote:
> I spent some time over the weekend trying out the different modes of
> parallel query (seq scan, aggregate, join) in combination with PostGIS
> and have written up the results here:
>
> http://blog.cleverelephant.ca/2016/03/parallel-postgis.html
>
> The TL:DR; is basically
>
> * With some adjustments to function COST both parallel sequence scan
> and parallel aggregation deliver very good parallel performance
> results.
> * The cost adjustments for sequence scan and aggregate scan are not
> consistent in magnitude.
> * Parallel join does not seem to work for PostGIS indexes yet, but
> perhaps there is some magic to learn from PostgreSQL core on that.
>
> The two findings at the end are ones that need input from parallel
> query masters...
>
> We recognize we'll have to adjust costs to that our particular use
> case (very CPU-intensive calculation per function) is planned better,
> but it seems like different query modes are interpreting costs in
> order-of-magnitude different ways in building plans.
>
> Parallel join would be a huge win, so some help/pointers on figuring
> out why it's not coming into play when our gist operators are in
> effect would be helpful.

First, I beg to differ with this statement: "Some of the execution
results output are wrong! They say that only 1844 rows were removed by
the filter, but in fact 7376 were (as we can confirm by running the
queries without the EXPLAIN ANALYZE). This is a known limitation,
reporting on the results of only one parallel worker, which (should)
maybe, hopefully be fixed before 9.6 comes out." The point is that
line has loops=4, so as in any other case where loops>1, you're seeing
the number of rows divided by the number of loops. It is the
*average* number of rows that were processed by each loop - one loop
per worker, in this case.

I am personally of the opinion that showing rowcounts divided by loops
instead of total rowcounts is rather stupid, and that we should change
it regardless. But it's not parallel query's fault, and changing it
would affect the output of every EXPLAIN ANALYZE involving a nested
loop, probably confusing a lot of people until they figured out what
we'd changed, after which - I *think* they'd realize that they
actually liked the new way much better.

Now, on to your actual question:

I have no idea why the cost adjustments that you need are different
for the scan case and the aggregate case. That does seem problematic,
but I just don't know why it's happening.

On the join case, I wonder if it's possible that _st_intersects is not
marked parallel-safe? If that's not the problem, I don't have a
second guess, but the thing to do would be to figure out whether
consider_parallel is false for the RelOptInfo corresponding to either
of pd and pts, or whether it's true for both but false for the
joinrel's RelOptInfo, or whether it's true for all three of them but
you don't get the desired path anyway.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company