public inbox for [email protected]  

help / color / mirror / Atom feed

[PATCH 3/6] Add primary_slot_name to init_from_backup in TAP test.
8+ messages / 5 participants
[nested] [flat]

* [PATCH 3/6] Add primary_slot_name to init_from_backup in TAP test.
@ 2018-12-19 03:43  Kyotaro Horiguchi <[email protected]>
  0 siblings, 0 replies; 8+ messages in thread

From: Kyotaro Horiguchi @ 2018-12-19 03:43 UTC (permalink / raw)

It is convenient that priary_slot_name can be specified on taking a
base backup. This adds a new parameter of the name to the perl
function.
---
 src/test/perl/PostgresNode.pm | 11 ++++++-----
 1 file changed, 6 insertions(+), 5 deletions(-)

diff --git a/src/test/perl/PostgresNode.pm b/src/test/perl/PostgresNode.pm
index 6019f37f91..2c7a894914 100644
--- a/src/test/perl/PostgresNode.pm
+++ b/src/test/perl/PostgresNode.pm
@@ -680,11 +680,7 @@ sub init_from_backup
 	chmod(0700, $data_path);
 
 	# Base configuration for this node
-	$self->append_conf(
-		'postgresql.conf',
-		qq(
-port = $port
-));
+	$self->append_conf('postgresql.conf', qq(port = $port));
 	if ($use_tcp)
 	{
 		$self->append_conf('postgresql.conf', "listen_addresses = '$host'");
@@ -694,6 +690,11 @@ port = $port
 		$self->append_conf('postgresql.conf',
 			"unix_socket_directories = '$host'");
 	}
+	$self->append_conf('postgresql.conf', qq(port = $port));
+	$self->append_conf('postgresql.conf',
+		qq(primary_slot_name = $params{primary_slot_name}))
+		if defined $params{primary_slot_name};
+
 	$self->enable_streaming($root_node) if $params{has_streaming};
 	$self->enable_restoring($root_node) if $params{has_restoring};
 	return;
-- 
2.20.1


--MP_/68vU=6Ib.yoYOTdA.M=EVne
Content-Type: text/x-patch
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename=v14-0004-TAP-test-for-the-slot-limit-feature.patch



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Todo: Teach planner to evaluate multiple windows in the optimal order
@ 2023-01-04 15:30  Ankit Kumar Pandey <[email protected]>
  parent: Vik Fearing <[email protected]>
  2 siblings, 1 reply; 8+ messages in thread

From: Ankit Kumar Pandey @ 2023-01-04 15:30 UTC (permalink / raw)
  To: Vik Fearing <[email protected]>; David Rowley <[email protected]>; +Cc: pghackers <[email protected]>


On 05/01/23 07:48, Vik Fearing wrote:
> On 1/4/23 13:07, Ankit Kumar Pandey wrote:
>> Also, one thing, consider the following query:
>>
>> explain analyze select row_number() over (order by a,b),count(*) over 
>> (order by a) from abcd order by a,b,c;
>>
>> In this case, sorting is done on (a,b) followed by incremental sort 
>> on c at final stage.
>>
>> If we do just one sort: a,b,c at first stage then there won't be need 
>> to do another sort (incremental one).
>
>
> This could give incorrect results.  Consider the following query:
>
> postgres=# select a, b, c, rank() over (order by a, b)
> from (values (1, 2, 1), (1, 2, 2), (1, 2, 1)) as abcd (a, b, c)
> order by a, b, c;
>
>  a | b | c | rank
> ---+---+---+------
>  1 | 2 | 1 |    1
>  1 | 2 | 1 |    1
>  1 | 2 | 2 |    1
> (3 rows)
>
>
> If you change the window's ordering like you suggest, you get this 
> different result:
>
>
> postgres=# select a, b, c, rank() over (order by a, b, c)
> from (values (1, 2, 1), (1, 2, 2), (1, 2, 1)) as abcd (a, b, c)
> order by a, b, c;
>
>  a | b | c | rank
> ---+---+---+------
>  1 | 2 | 1 |    1
>  1 | 2 | 1 |    1
>  1 | 2 | 2 |    3
> (3 rows)
>
>
We are already doing something like I mentioned.

Consider this example:

explain SELECT rank() OVER (ORDER BY a), count(*) OVER (ORDER BY a,b) 
FROM abcd;
                                 QUERY PLAN
--------------------------------------------------------------------------
  WindowAgg  (cost=83.80..127.55 rows=1250 width=24)
    ->  WindowAgg  (cost=83.80..108.80 rows=1250 width=16)
          ->  Sort  (cost=83.80..86.92 rows=1250 width=8)
                Sort Key: a, b
                ->  Seq Scan on abcd  (cost=0.00..19.50 rows=1250 width=8)
(5 rows)


If it is okay to do extra sort for first window function (rank) here, 
why would it be

any different in case which I mentioned?

My suggestion rest on assumption that for a window function, say

rank() OVER (ORDER BY a), ordering of columns (other than column 'a') 
shouldn't matter.


-- 
Regards,
Ankit Kumar Pandey







^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Todo: Teach planner to evaluate multiple windows in the optimal order
@ 2023-01-05 02:18  Vik Fearing <[email protected]>
  0 siblings, 3 replies; 8+ messages in thread

From: Vik Fearing @ 2023-01-05 02:18 UTC (permalink / raw)
  To: Ankit Kumar Pandey <[email protected]>; David Rowley <[email protected]>; +Cc: pghackers <[email protected]>

On 1/4/23 13:07, Ankit Kumar Pandey wrote:
> Also, one thing, consider the following query:
> 
> explain analyze select row_number() over (order by a,b),count(*) over 
> (order by a) from abcd order by a,b,c;
> 
> In this case, sorting is done on (a,b) followed by incremental sort on c 
> at final stage.
> 
> If we do just one sort: a,b,c at first stage then there won't be need to 
> do another sort (incremental one).


This could give incorrect results.  Consider the following query:

postgres=# select a, b, c, rank() over (order by a, b)
from (values (1, 2, 1), (1, 2, 2), (1, 2, 1)) as abcd (a, b, c)
order by a, b, c;

  a | b | c | rank
---+---+---+------
  1 | 2 | 1 |    1
  1 | 2 | 1 |    1
  1 | 2 | 2 |    1
(3 rows)


If you change the window's ordering like you suggest, you get this 
different result:


postgres=# select a, b, c, rank() over (order by a, b, c)
from (values (1, 2, 1), (1, 2, 2), (1, 2, 1)) as abcd (a, b, c)
order by a, b, c;

  a | b | c | rank
---+---+---+------
  1 | 2 | 1 |    1
  1 | 2 | 1 |    1
  1 | 2 | 2 |    3
(3 rows)


-- 
Vik Fearing







^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Todo: Teach planner to evaluate multiple windows in the optimal order
@ 2023-01-05 02:30  David Rowley <[email protected]>
  parent: Vik Fearing <[email protected]>
  2 siblings, 1 reply; 8+ messages in thread

From: David Rowley @ 2023-01-05 02:30 UTC (permalink / raw)
  To: Vik Fearing <[email protected]>; +Cc: Ankit Kumar Pandey <[email protected]>; pghackers <[email protected]>

On Thu, 5 Jan 2023 at 15:18, Vik Fearing <[email protected]> wrote:
>
> On 1/4/23 13:07, Ankit Kumar Pandey wrote:
> > Also, one thing, consider the following query:
> >
> > explain analyze select row_number() over (order by a,b),count(*) over
> > (order by a) from abcd order by a,b,c;
> >
> > In this case, sorting is done on (a,b) followed by incremental sort on c
> > at final stage.
> >
> > If we do just one sort: a,b,c at first stage then there won't be need to
> > do another sort (incremental one).
>
>
> This could give incorrect results.

Yeah, this seems to be what I warned against in [1].

If we wanted to make that work we'd need to do it without adjusting
the WindowClause's orderClause so that the peer row checks still
worked correctly in nodeWindowAgg.c.

Additionally, it's also not that clear to me that sorting by more
columns in the sort below the WindowAgg would always be a win over
doing the final sort for the ORDER BY.  What if the WHERE clause (that
could not be pushed down before a join) filtered out the vast majority
of the rows before the ORDER BY. It might be cheaper to do the sort
then than to sort by the additional columns earlier.

David

[1] https://www.postgresql.org/message-id/[email protected]...






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Todo: Teach planner to evaluate multiple windows in the optimal order
@ 2023-01-05 02:48  Tom Lane <[email protected]>
  parent: Vik Fearing <[email protected]>
  2 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2023-01-05 02:48 UTC (permalink / raw)
  To: Vik Fearing <[email protected]>; +Cc: Ankit Kumar Pandey <[email protected]>; David Rowley <[email protected]>; pghackers <[email protected]>

Vik Fearing <[email protected]> writes:
> On 1/4/23 13:07, Ankit Kumar Pandey wrote:
>> Also, one thing, consider the following query:
>> explain analyze select row_number() over (order by a,b),count(*) over 
>> (order by a) from abcd order by a,b,c;
>> In this case, sorting is done on (a,b) followed by incremental sort on c 
>> at final stage.
>> If we do just one sort: a,b,c at first stage then there won't be need to 
>> do another sort (incremental one).

> This could give incorrect results.

Mmmm ... your counterexample doesn't really prove that.  Yes,
the "rank()" step must consider only two ORDER BY columns while
deciding which rows are peers, but I don't see why it wouldn't
be okay if the rows happened to already be sorted by "c" within
those peer groups.

I don't recall the implementation details well enough to be sure
how hard it would be to keep that straight.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Todo: Teach planner to evaluate multiple windows in the optimal order
@ 2023-01-05 03:12  Tom Lane <[email protected]>
  parent: David Rowley <[email protected]>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2023-01-05 03:12 UTC (permalink / raw)
  To: David Rowley <[email protected]>; +Cc: Vik Fearing <[email protected]>; Ankit Kumar Pandey <[email protected]>; pghackers <[email protected]>

David Rowley <[email protected]> writes:
> Additionally, it's also not that clear to me that sorting by more
> columns in the sort below the WindowAgg would always be a win over
> doing the final sort for the ORDER BY.  What if the WHERE clause (that
> could not be pushed down before a join) filtered out the vast majority
> of the rows before the ORDER BY. It might be cheaper to do the sort
> then than to sort by the additional columns earlier.

That's certainly a legitimate question to ask, but I don't quite see
where you figure we'd be sorting more rows?  WHERE filtering happens
before window functions, which never eliminate any rows.  So it seems
like a sort just before the window functions must sort the same number
of rows as one just after them.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Todo: Teach planner to evaluate multiple windows in the optimal order
@ 2023-01-05 07:14  David Rowley <[email protected]>
  parent: Tom Lane <[email protected]>
  0 siblings, 0 replies; 8+ messages in thread

From: David Rowley @ 2023-01-05 07:14 UTC (permalink / raw)
  To: Tom Lane <[email protected]>; +Cc: Vik Fearing <[email protected]>; Ankit Kumar Pandey <[email protected]>; pghackers <[email protected]>

On Thu, 5 Jan 2023 at 16:12, Tom Lane <[email protected]> wrote:
>
> David Rowley <[email protected]> writes:
> > Additionally, it's also not that clear to me that sorting by more
> > columns in the sort below the WindowAgg would always be a win over
> > doing the final sort for the ORDER BY.  What if the WHERE clause (that
> > could not be pushed down before a join) filtered out the vast majority
> > of the rows before the ORDER BY. It might be cheaper to do the sort
> > then than to sort by the additional columns earlier.
>
> That's certainly a legitimate question to ask, but I don't quite see
> where you figure we'd be sorting more rows?  WHERE filtering happens
> before window functions, which never eliminate any rows.  So it seems
> like a sort just before the window functions must sort the same number
> of rows as one just after them.

Yeah, I didn't think the WHERE clause thing out carefully enough. I
think it's only the WindowClause's runCondition that could possibly
filter any rows between the Sort below the WindowAgg and before the
ORDER BY is evaluated.

David






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Todo: Teach planner to evaluate multiple windows in the optimal order
@ 2023-01-05 07:23  David Rowley <[email protected]>
  parent: Ankit Kumar Pandey <[email protected]>
  0 siblings, 0 replies; 8+ messages in thread

From: David Rowley @ 2023-01-05 07:23 UTC (permalink / raw)
  To: Ankit Kumar Pandey <[email protected]>; +Cc: Vik Fearing <[email protected]>; pghackers <[email protected]>

On Thu, 5 Jan 2023 at 19:14, Ankit Kumar Pandey <[email protected]> wrote:
> We are already doing something like I mentioned.
>
> Consider this example:
>
> explain SELECT rank() OVER (ORDER BY a), count(*) OVER (ORDER BY a,b)
> FROM abcd;
>                                  QUERY PLAN
> --------------------------------------------------------------------------
>   WindowAgg  (cost=83.80..127.55 rows=1250 width=24)
>     ->  WindowAgg  (cost=83.80..108.80 rows=1250 width=16)
>           ->  Sort  (cost=83.80..86.92 rows=1250 width=8)
>                 Sort Key: a, b
>                 ->  Seq Scan on abcd  (cost=0.00..19.50 rows=1250 width=8)
> (5 rows)
>
>
> If it is okay to do extra sort for first window function (rank) here,
> why would it be
>
> any different in case which I mentioned?

We *can* reuse Sorts where a more strict or equivalent sort order is
available.  The question is how do we get the final WindowClause to do
something slightly more strict to save having to do anything for the
ORDER BY.  One way you might think would be to adjust the
WindowClause's orderClause to add the additional clauses, but that
cannot be done because that would cause are_peers() in nodeWindowAgg.c
to not count some rows as peers when they maybe should be given a less
strict orderClause in the WindowClause.

It might be possible to adjust create_one_window_path() so that when
processing the final WindowClause that it looks at the DISTINCT or
ORDER BY clause to see if we can sort on a few extra columns to save
having to do any further sorting.  We just *cannot* make any
adjustments to the WindowClause's orderClause.

David






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

end of thread, other threads:[~2023-01-05 07:23 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-12-19 03:43 [PATCH 3/6] Add primary_slot_name to init_from_backup in TAP test. Kyotaro Horiguchi <[email protected]>
2023-01-05 02:18 Re: Todo: Teach planner to evaluate multiple windows in the optimal order Vik Fearing <[email protected]>
2023-01-04 15:30 ` Re: Todo: Teach planner to evaluate multiple windows in the optimal order Ankit Kumar Pandey <[email protected]>
2023-01-05 07:23   ` Re: Todo: Teach planner to evaluate multiple windows in the optimal order David Rowley <[email protected]>
2023-01-05 02:30 ` Re: Todo: Teach planner to evaluate multiple windows in the optimal order David Rowley <[email protected]>
2023-01-05 03:12   ` Re: Todo: Teach planner to evaluate multiple windows in the optimal order Tom Lane <[email protected]>
2023-01-05 07:14     ` Re: Todo: Teach planner to evaluate multiple windows in the optimal order David Rowley <[email protected]>
2023-01-05 02:48 ` Re: Todo: Teach planner to evaluate multiple windows in the optimal order Tom Lane <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox