Re: [HACKERS] Perfomance bug in v10

2017-06-02 Thread Tom Lane
Teodor Sigaev writes: >> BTW, was the larger query plan that you showed (with a Materialize node) >> generated by 9.6, or v10 HEAD? Because I would be surprised if 9.6 did > v10, > commit acbd8375e954774181b673a31b814e9d46f436a5 > Author: Magnus Hagander

Re: [HACKERS] Perfomance bug in v10

2017-06-02 Thread Teodor Sigaev
BTW, was the larger query plan that you showed (with a Materialize node) generated by 9.6, or v10 HEAD? Because I would be surprised if 9.6 did v10, commit acbd8375e954774181b673a31b814e9d46f436a5 Author: Magnus Hagander Date: Fri Jun 2 11:18:24 2017 +0200 -- Teodor

Re: [HACKERS] Perfomance bug in v10

2017-06-02 Thread Tom Lane
Teodor Sigaev writes: >> There were old threads about considering a risk factor when estimating >> plans, and I'm thinking this issue is the planner failing to do >> exactly that. > I'm afraid it's tool late for v10 Yeah, we're surely not opening that can of worms for v10.

Re: [HACKERS] Perfomance bug in v10

2017-06-02 Thread Claudio Freire
On Fri, Jun 2, 2017 at 11:46 AM, Teodor Sigaev wrote: >> There were old threads about considering a risk factor when estimating >> plans, and I'm thinking this issue is the planner failing to do >> exactly that. >> > I'm afraid it's tool late for v10 Clearly -- Sent via

Re: [HACKERS] Perfomance bug in v10

2017-06-02 Thread Teodor Sigaev
There were old threads about considering a risk factor when estimating plans, and I'm thinking this issue is the planner failing to do exactly that. I'm afraid it's tool late for v10 -- Teodor Sigaev E-mail: teo...@sigaev.ru

Re: [HACKERS] Perfomance bug in v10

2017-06-02 Thread Claudio Freire
On Fri, Jun 2, 2017 at 10:27 AM, Tom Lane wrote: > Teodor Sigaev writes: >>> Teodor, could you check if this patch fixes your real-world problem? > >> It works fine with original query, thank you. But some other query slowdowns >> for >> ~10% (9 secs vs 10

Re: [HACKERS] Perfomance bug in v10

2017-06-02 Thread Tom Lane
Teodor Sigaev writes: >> Teodor, could you check if this patch fixes your real-world problem? > It works fine with original query, thank you. But some other query slowdowns > for > ~10% (9 secs vs 10 secs). Look at following part of plans of huge query: > ... > As you said,

Re: [HACKERS] Perfomance bug in v10

2017-06-02 Thread Teodor Sigaev
Teodor, could you check if this patch fixes your real-world problem? It works fine with original query, thank you. But some other query slowdowns for ~10% (9 secs vs 10 secs). Look at following part of plans of huge query: without patch: -> Nested Loop (cost=34.82..50.91 rows=1 width=20)

Re: [HACKERS] Perfomance bug in v10

2017-06-01 Thread Tom Lane
David Rowley writes: > On 1 June 2017 at 04:16, Teodor Sigaev wrote: >> I found an example where v10 chooses extremely non-optimal plan: >> ... > This is all caused by get_variable_numdistinct() deciding that all > values are distinct because

Re: [HACKERS] Perfomance bug in v10

2017-06-01 Thread David Rowley
On 2 June 2017 at 03:46, Teodor Sigaev wrote: > I miss here why could the presence of index influence on that? removing > index causes a good plan although it isn't used in both plans . Unique indexes are used as proofs when deciding if a join to the relation is "inner_unique".

Re: [HACKERS] Perfomance bug in v10

2017-06-01 Thread Teodor Sigaev
Thank you for the answer! This is all caused by get_variable_numdistinct() deciding that all values are distinct because ntuples < DEFAULT_NUM_DISTINCT. I see that if the example is increased to use 300 tuples instead of 32, then that's enough for the planner to estimate 2 rows instead of

Re: [HACKERS] Perfomance bug in v10

2017-05-31 Thread David Rowley
On 1 June 2017 at 04:16, Teodor Sigaev wrote: > I found an example where v10 chooses extremely non-optimal plan: > select > i::int as a, > i::int + 1 as b, > 0 as c > into t > from > generate_series(1,32) as i; > > create unique index i on t (c, a); > >