Re: planner chooses incremental but not the best one

On 2/15/24 13:45, Andrei Lepikhov wrote: > On 15/2/2024 18:10, Tomas Vondra wrote: >> >> >> On 2/15/24 07:50, Andrei Lepikhov wrote: >>> On 18/12/2023 19:53, Tomas Vondra wrote: On 12/18/23 11:40, Richard Guo wrote: The challenge is where to get usable information about correlation >>>

Re: planner chooses incremental but not the best one

On 15/2/2024 18:10, Tomas Vondra wrote: On 2/15/24 07:50, Andrei Lepikhov wrote: On 18/12/2023 19:53, Tomas Vondra wrote: On 12/18/23 11:40, Richard Guo wrote: The challenge is where to get usable information about correlation between columns. I only have a couple very rought ideas of what mi

Re: planner chooses incremental but not the best one

On 2/15/24 07:50, Andrei Lepikhov wrote: > On 18/12/2023 19:53, Tomas Vondra wrote: >> On 12/18/23 11:40, Richard Guo wrote: >> The challenge is where to get usable information about correlation >> between columns. I only have a couple very rought ideas of what might >> try. For example, if we h

Re: planner chooses incremental but not the best one

On 15/12/2023 15:58, Richard Guo wrote: With the patch the estimate for the number of distinct 'b' values is more accurate. +1 to commit this patch. It looks good and resolves kind of a bug in the code. BTW, this patch does not change any existing regression test results.  I attempted to devis

Re: planner chooses incremental but not the best one

On 18/12/2023 19:53, Tomas Vondra wrote: On 12/18/23 11:40, Richard Guo wrote: The challenge is where to get usable information about correlation between columns. I only have a couple very rought ideas of what might try. For example, if we have multi-column ndistinct statistics, we might look at

Re: planner chooses incremental but not the best one

Hi，Tomas Recently, I looked at papers related to estimation of cardinarity with selection. I may be biased towards the scheme provided by the paper "Distinct Sampling for Highly-Accurate Answers to Distinct Values Queries and Event Reports". This paper uses distinct sampling as opposed to the curr

Re: planner chooses incremental but not the best one

On 15/12/2023 09:58, Richard Guo wrote: On Thu, Dec 14, 2023 at 6:02 PM Richard Guo wrote: It seems that we need to improve estimate of distinct values in estimate_num_groups() when taking the selectivity of restrictions into account. In 84f9a35e3 we changed to a new formula

Re: planner chooses incremental but not the best one

The possible solution of one scenario I can come up with so far is the query's predicate columns and group columns belonging to one table . For a query that contains where clause, perhaps num_groups could be estimated according to the following formula. num_groups = ndistinct(pred_col_1, pred_col

Re: planner chooses incremental but not the best one

On 12/18/23 11:40, Richard Guo wrote: > > On Mon, Dec 18, 2023 at 7:31 AM Tomas Vondra > mailto:tomas.von...@enterprisedb.com>> > wrote: > > Oh! Now I see what you meant by using the new formula in 84f9a35e3 > depending on how we sum tuples. I agree that seems like the right thing. >

Re: planner chooses incremental but not the best one

On Mon, Dec 18, 2023 at 7:31 AM Tomas Vondra wrote: > Oh! Now I see what you meant by using the new formula in 84f9a35e3 > depending on how we sum tuples. I agree that seems like the right thing. > > I'm not sure it'll actually help with the issue, though - if I apply the > patch, the plan does n

Re: planner chooses incremental but not the best one

On 12/14/23 11:02, Richard Guo wrote: > > On Tue, Dec 12, 2023 at 4:40 PM Nicolas Lutic > wrote: > > I've come across a behaviour of the planner I can't explain. > After a migration from 11 to 15 (on RDS) we noticed a degradation in > response time on

Re: planner chooses incremental but not the best one

Tomas Vondra writes: > Yeah, seems like that's the right thing to do. FWIW I've been often > confused by these fields, because we use tuples and rows as synonyms, > but in this particular case that's not the same. I wonder if this is > just a manifestation of this confusion. For tables, one is th

Re: planner chooses incremental but not the best one

On 12/15/23 09:58, Richard Guo wrote: > > On Thu, Dec 14, 2023 at 6:02 PM Richard Guo > wrote: > > It seems that we need to improve estimate of distinct values in > estimate_num_groups() when taking the selectivity of restrictions into > account. >

Re: planner chooses incremental but not the best one

On Thu, Dec 14, 2023 at 6:02 PM Richard Guo wrote: > It seems that we need to improve estimate of distinct values in > estimate_num_groups() when taking the selectivity of restrictions into > account. > > In 84f9a35e3 we changed to a new formula to perform such estimation. > But that does not app

Re: planner chooses incremental but not the best one

On Tue, Dec 12, 2023 at 4:40 PM Nicolas Lutic wrote: > I've come across a behaviour of the planner I can't explain. > After a migration from 11 to 15 (on RDS) we noticed a degradation in > response time on a query, it went from a few seconds to ten minutes. > A vacuum(analyze) has been realized t

planner chooses incremental but not the best one

Dear Hackers, I've come across a behaviour of the planner I can't explain. After a migration from 11 to 15 (on RDS) we noticed a degradation in response time on a query, it went from a few seconds to ten minutes. A vacuum(analyze) has been realized to be sure that all is clean. The 'explain an