Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Sun, Jun 11, 2017 at 4:10 PM, Tomas Vondra wrote: > I do strongly recommend reading this paper analyzing choke points of > individual TPC-H queries: > > http://oai.cwi.nl/oai/asset/21424/21424B.pdf > > It's slightly orthogonal to the issue at hand (poor

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

Hi, On 6/11/17 7:54 PM, Peter Geoghegan wrote: On Sun, Jun 11, 2017 at 10:36 AM, Tom Lane wrote: Do you mean teaching the optimizer to do something like this?: Uh, no. I don't think we want to add any run-time checks. The point in this example is that we'd get a better

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Sun, Jun 11, 2017 at 10:27 AM, Peter Geoghegan wrote: > Note that I introduced a new, redundant exists() in the agg_lineitem > fact table subquery. It now takes 23 seconds for me on Tomas' 10GB > TPC-H dataset, whereas the original query took over 90 minutes. > Clearly we're

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Sun, Jun 11, 2017 at 10:36 AM, Tom Lane wrote: >> Do you mean teaching the optimizer to do something like this?: > > Uh, no. I don't think we want to add any run-time checks. The point in > this example is that we'd get a better rowcount estimate if we noticed > that the

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

Peter Geoghegan writes: > On Thu, Jun 1, 2017 at 8:40 AM, Tom Lane wrote: >> The thing that would actually have a chance of improving matters for Q20 >> would be if we could see our way to looking through the aggregation >> subquery and applying the foreign key

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Thu, Jun 1, 2017 at 8:40 AM, Tom Lane wrote: > The thing that would actually have a chance of improving matters for Q20 > would be if we could see our way to looking through the aggregation > subquery and applying the foreign key constraint for lineitem. That > seems like

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

Tomas Vondra writes: > Which brings me to the slightly suspicious bit. On 9.5, there's no > difference between GROUP and GROUP+LIKE cases - the estimates are > exactly the same in both cases. This is true too, but only without the > foreign key between "partsupp"

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

Hi, On 5/25/17 6:03 AM, Robert Haas wrote: On Thu, Apr 6, 2017 at 4:37 PM, Tomas Vondra wrote: Which brings me to the slightly suspicious bit. On 9.5, there's no difference between GROUP and GROUP+LIKE cases - the estimates are exactly the same in both cases.

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Thu, Apr 6, 2017 at 4:37 PM, Tomas Vondra wrote: > Which brings me to the slightly suspicious bit. On 9.5, there's no > difference between GROUP and GROUP+LIKE cases - the estimates are exactly > the same in both cases. This is true too, but only without the

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

Hi, I've been looking at this issue today, and so far I don't think it's a bug in the foreign key estimation. It seems mostly that the 9.5 estimates were hopelessly bad, and the join estimation changes simply pushed it a tiny bit the wrong direction. Although maybe there is a bug (or at

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On 04/01/2017 02:57 AM, Robert Haas wrote: On Fri, Mar 31, 2017 at 7:53 AM, Rafia Sabih wrote: So, it looks like in the problematic area, it is not improving much. Please find the attached file for the query plan of Q20 with and without patch. I haven't

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Fri, Mar 31, 2017 at 7:53 AM, Rafia Sabih wrote: > So, it looks like in the problematic area, it is not improving much. > Please find the attached file for the query plan of Q20 with and > without patch. I haven't evaluated the performance of this query with >

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Fri, Mar 31, 2017 at 5:13 PM, Amit Kapila wrote: > On Thu, Mar 30, 2017 at 8:24 AM, Robert Haas wrote: >> On Wed, Mar 29, 2017 at 8:00 PM, Tomas Vondra >> wrote: >>> What is however strange is that changing

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Thu, Mar 30, 2017 at 8:24 AM, Robert Haas wrote: > On Wed, Mar 29, 2017 at 8:00 PM, Tomas Vondra > wrote: >> What is however strange is that changing max_parallel_workers_per_gather >> affects row estimates *above* the Gather node. That

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Thu, Mar 30, 2017 at 12:30 AM, Robert Haas wrote: > I don't think the problem originates at the Merge Join, though, > because the commit says that at is fixing semi and anti-join estimates > - this is a plain inner join, so in theory it shouldn't change. > However, it's

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Wed, Mar 29, 2017 at 8:00 PM, Tomas Vondra wrote: > What is however strange is that changing max_parallel_workers_per_gather > affects row estimates *above* the Gather node. That seems a bit, um, > suspicious, no? See the parallel-estimates.log. Thanks for

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On 03/30/2017 12:14 AM, Tomas Vondra wrote: I've only ran the queries on 10GB data set, but that should be enough. The plans are from current master - I'll rerun the script on an older release later today. So, an plans from an older release (9.4) are attached. What seems to matter is

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On 03/29/2017 09:00 PM, Robert Haas wrote: On Mon, Mar 6, 2017 at 1:22 AM, Rafia Sabih wrote: This is to bring to notice a peculiar instance I found recently while running TPC-H benchmark queries. Q20 of the benchmark took 19 hours to complete ... That's bad.

Re: [HACKERS] TPC-H Q20 from 1 hour to 19 hours!

On Mon, Mar 6, 2017 at 1:22 AM, Rafia Sabih wrote: > This is to bring to notice a peculiar instance I found recently while > running TPC-H benchmark queries. Q20 of the benchmark took 19 hours to > complete ... That's bad. > It is clear that selectivity estimations

[HACKERS] TPC-H Q20 from 1 hour to 19 hours!

Hello all, This is to bring to notice a peculiar instance I found recently while running TPC-H benchmark queries. Q20 of the benchmark took 19 hours to complete when run on a machine with 512 GB RAM and 32 cores with following parameter settings on the commit id -