Re: Parallel Queries and PostGIS - Mailing list pgsql-hackers
From | Robert Haas |
---|---|
Subject | Re: Parallel Queries and PostGIS |
Date | |
Msg-id | CA+TgmoaKHi_pDm6q+0nJZ45n_7mDZyaYUAE902uD1AG64cnDTA@mail.gmail.com Whole thread Raw |
In response to | Parallel Queries and PostGIS (Paul Ramsey <pramsey@cleverelephant.ca>) |
Responses |
Re: Parallel Queries and PostGIS
|
List | pgsql-hackers |
On Mon, Mar 28, 2016 at 12:18 PM, Paul Ramsey <pramsey@cleverelephant.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
pgsql-hackers by date: