On Thu, 1 Jul 2021 at 08:56, Manuel Weitzman wrote:
> For each of these RestrictInfos there *could* be one cache miss on
> cached_scansel() that *could* force the planner to compute
> get_actual_variable_range() for the same variable (a.a) over and over,
> as mergejoinscansel() always computes the
> On 30-06-2021, at 16:56, Manuel Weitzman wrote:
>
> One way in which I see possible to share this kind of information (of
> extremal values) across RestrictInfos is to store the known variable
> ranges in PlannerInfo (or within a member of such struct), which seems
> to be around everywhere it
> 1. create_join_clause doesn't trouble to look for commuted
> equivalents, which perhaps is penny-wise and pound-foolish.
> The cost of re-deriving selectivity estimates could be way
> more than the cost of checking this.
Agreed.
> 2. Although these look like they ought to be equivalent to the
>
Manuel Weitzman writes:
> On 29-06-2021, at 15:43, Tom Lane wrote:
>> That seems a bit broken; a given WHERE clause should produce only one
>> RestrictInfo. Can you provide a more concrete example?
>> explain (analyze, buffers)
>> select * from a
>> join b b1 on (b1.a = a.a)
>> join b b2 on (b2
> On 29-06-2021, at 15:43, Tom Lane wrote:
>
> Manuel Weitzman writes:
>>> On 20-06-2021, at 17:06, Tom Lane wrote:
>>> So ... the reason why there's not caching of get_actual_variable_range
>>> results already is that I'd supposed it wouldn't be necessary given
>>> the caching of selectivity
Manuel Weitzman writes:
>> On 20-06-2021, at 17:06, Tom Lane wrote:
>> So ... the reason why there's not caching of get_actual_variable_range
>> results already is that I'd supposed it wouldn't be necessary given
>> the caching of selectivity estimates that happens at the RestrictInfo
>> level.
> On 20-06-2021, at 17:06, Tom Lane wrote:
>
> So ... the reason why there's not caching of get_actual_variable_range
> results already is that I'd supposed it wouldn't be necessary given
> the caching of selectivity estimates that happens at the RestrictInfo
> level. I don't have any objectio
Ranier Vilela writes:
> 3. Avoid use of type *long*, it is very problematic with 64 bits.
> Windows 64 bits, long is 4 (four) bytes.
> Linux 64 bits, long is 8 (eight) bytes.
Agreed.
> 4. Avoid C99 style declarations
> for(unsigned long i = 0;)
> Prefer:
>size_t i;
>for(i = 0;)
> Hel
Em dom., 20 de jun. de 2021 às 14:50, Manuel Weitzman <
manuelweitz...@gmail.com> escreveu:
> Hello everyone,
>
> > Apparently, the planner isn't reusing the data boundaries across
> alternative
> > plans. It would be nicer if the planner remembered each column boundaries
> > for later reuse (with
Manuel Weitzman writes:
> I've written a very naive (and crappy) patch to show how adding
> memorization to get_actual_variable_range() could help the planner on
> scenarios with a big number of joins.
So ... the reason why there's not caching of get_actual_variable_range
results already is that
Hello everyone,
> Apparently, the planner isn't reusing the data boundaries across alternative
> plans. It would be nicer if the planner remembered each column boundaries
> for later reuse (within the same planner execution).
I've written a very naive (and crappy) patch to show how adding
memoriz
> However, I'm skeptical that any problem actually remains in
> real-world use cases.
Hello Tom,
We also had some issues with planning and get_actual_variable_range(). We
actually found some interesting behaviour that probably requires an eye with
better expertise in how the planner works.
For th
David Rowley writes:
> FWIW, here's a simple test case that shows the problem in current master.
This isn't telling the whole story. That first EXPLAIN did set the killed
bits in the index, so that subsequent ones are fairly fast, even without
VACUUM:
regression=# explain select * from a where
On Thu, 22 Apr 2021 at 00:03, Jeremy Schneider wrote:
>
> Two years later, I still remember this. And today I just confirmed
> someone hitting this on open source PG13.
The only thing that changed about get_actual_variable_range() is that
it now uses a SnapshotNonVacuumable snapshot. Previously a
Hi,
On 2019-04-08 16:10:17 -0700, Jeremy Schneider wrote:
> On 4/8/19 07:42, Justin Pryzby wrote:
> > On Mon, Apr 08, 2019 at 04:33:34PM +0200, Pavel Stehule wrote:
> >> po 8. 4. 2019 v 16:11 odesílatel Krzysztof Plocharz
> >> napsal:
> >>
> >>> We have some very strange query planning problem.
On Mon, Apr 08, 2019 at 04:55:36PM +0200, Krzysztof Plocharz wrote:
> We did pgrepack and it did help, but is it possible for
> get_actual_variable_range to take over 60 seconds?
You have many tables being joined, perhaps in exhaustive search, so maybe
that's being called many times.
What version
po 8. 4. 2019 v 16:55 odesílatel Krzysztof Plocharz
napsal:
>
>
> On 2019/04/08 16:42, Justin Pryzby wrote:
> > On Mon, Apr 08, 2019 at 04:33:34PM +0200, Pavel Stehule wrote:
> >> po 8. 4. 2019 v 16:11 odes�latel Krzysztof Plocharz <
> ploch...@9livesdata.com> napsal:
> >>
> >>> We have some very
On 2019/04/08 16:33, Pavel Stehule wrote:
po 8. 4. 2019 v 16:11 odesílatel Krzysztof Plocharz
mailto:ploch...@9livesdata.com>> napsal:
Hi
We have some very strange query planning problem. Long story short it
takes 67626.278ms just to plan. Query execution takes 12ms.
Que
On 2019/04/08 16:42, Justin Pryzby wrote:
On Mon, Apr 08, 2019 at 04:33:34PM +0200, Pavel Stehule wrote:
po 8. 4. 2019 v 16:11 odes�latel Krzysztof Plocharz
napsal:
We have some very strange query planning problem. Long story short it
takes 67626.278ms just to plan. Query execution takes
On Mon, Apr 08, 2019 at 04:33:34PM +0200, Pavel Stehule wrote:
> po 8. 4. 2019 v 16:11 odesílatel Krzysztof Plocharz
> napsal:
>
> > We have some very strange query planning problem. Long story short it
> > takes 67626.278ms just to plan. Query execution takes 12ms.
> >
> > Query has 7 joins and
po 8. 4. 2019 v 16:11 odesílatel Krzysztof Plocharz
napsal:
> Hi
>
> We have some very strange query planning problem. Long story short it
> takes 67626.278ms just to plan. Query execution takes 12ms.
>
> Query has 7 joins and 2 subselects.
> It looks like the issue is not deterministic, sometime
No, Autovacuum is running.
On 2019/04/08 16:18, Igor Neyman wrote:
-Original Message-
From: Krzysztof Plocharz [mailto:ploch...@9livesdata.com]
Sent: Monday, April 08, 2019 10:11 AM
To: pgsql-performance@lists.postgresql.org
Subject: Planning performance problem (67626.278ms)
Hi
We
-Original Message-
From: Krzysztof Plocharz [mailto:ploch...@9livesdata.com]
Sent: Monday, April 08, 2019 10:11 AM
To: pgsql-performance@lists.postgresql.org
Subject: Planning performance problem (67626.278ms)
Hi
We have some very strange query planning problem. Long story short it
Hi
We have some very strange query planning problem. Long story short it
takes 67626.278ms just to plan. Query execution takes 12ms.
Query has 7 joins and 2 subselects.
It looks like the issue is not deterministic, sometimes is takes few ms
to plan the query.
One of the tables has 550,485,9
24 matches
Mail list logo