It would be nice to clarify is there any point in select queries pipelining

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: evgeny(dot)v(dot)smirnov(at)gmail(dot)com
Subject: It would be nice to clarify is there any point in select queries pipelining
Date: 2024-01-25 04:46:56
Message-ID: 170615801656.662.17755050278063339844@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/16/protocol-flow.html
Description:

Greeting!
Please consider the following exchange with a PG database (Kotlin + Reactor+
r2dbc-postgresql):
```
295 1.063166 127.0.0.1 50591 127.0.0.1 32797 PGSQL 111
>Q ---> BEGIN ISOLATION LEVEL REPEATABLE READ, READ WRITE
296 1.063219 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=1 Ack=56 Win=6373 Len=0 TSval=3266177882
TSecr=3728690767
301 1.069912 127.0.0.1 32797 127.0.0.1 50591 PGSQL 73
<C/Z
302 1.069938 127.0.0.1 50591 127.0.0.1 32797 TCP 56
50591 → 32797 [ACK] Seq=56 Ack=18 Win=6370 Len=0 TSval=3728690774
TSecr=3266177889
712 1.099829 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551
>Q ------> select * from ... (#2)
713 1.099858 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=18 Ack=551 Win=6365 Len=0 TSval=3266177919
TSecr=3728690804
715 1.099985 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551
>Q ------> select * from ... (#4)
717 1.100009 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=18 Ack=1046 Win=6358 Len=0 TSval=3266177919
TSecr=3728690804
719 1.100082 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551
>Q ------> select * from ... (#1)
720 1.100106 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=18 Ack=1541 Win=6350 Len=0 TSval=3266177919
TSecr=3728690804
722 1.100164 127.0.0.1 50591 127.0.0.1 32797 PGSQL 551
>Q ------> select * from ... (#3)
723 1.100192 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=18 Ack=2036 Win=6342 Len=0 TSval=3266177919
TSecr=3728690804
735 1.114695 127.0.0.1 32797 127.0.0.1 50591 PGSQL 424
<T/D/C/Z ------> Results for #2
737 1.114741 127.0.0.1 50591 127.0.0.1 32797 TCP 56
50591 → 32797 [ACK] Seq=2036 Ack=386 Win=6364 Len=0 TSval=3728690818
TSecr=3266177933
773 1.121732 127.0.0.1 32797 127.0.0.1 50591 PGSQL 1468
<T/D/D/D/D/D/D/D/D/D/D/D/D/C/Z ------> Results for #4
774 1.121757 127.0.0.1 50591 127.0.0.1 32797 TCP 56
50591 → 32797 [ACK] Seq=2036 Ack=1798 Win=6342 Len=0 TSval=3728690826
TSecr=3266177941
785 1.126793 127.0.0.1 32797 127.0.0.1 50591 PGSQL 594
<T/D/D/D/C/Z ------> Results for #1
786 1.126820 127.0.0.1 50591 127.0.0.1 32797 TCP 56
50591 → 32797 [ACK] Seq=2036 Ack=2336 Win=6334 Len=0 TSval=3728690831
TSecr=3266177946
805 1.135197 127.0.0.1 32797 127.0.0.1 50591 PGSQL 497
<T/D/D/C/Z ------> Results for #3
806 1.135222 127.0.0.1 50591 127.0.0.1 32797 TCP 56
50591 → 32797 [ACK] Seq=2036 Ack=2777 Win=6327 Len=0 TSval=3728690839
TSecr=3266177954
847 1.138848 127.0.0.1 50591 127.0.0.1 32797 PGSQL 68
>Q ------> COMMIT
848 1.138876 127.0.0.1 32797 127.0.0.1 50591 TCP 56
32797 → 50591 [ACK] Seq=2777 Ack=2048 Win=6342 Len=0 TSval=3266177958
TSecr=3728690843
853 1.144624 127.0.0.1 32797 127.0.0.1 50591 PGSQL 74
<C/Z
```

The application code is the following (for the sake of clarity):
```
val transactionalOperator = TransactionalOperator.create(
transactionManager,
DefaultTransactionDefinition() .apply {
isolationLevel = TransactionDefinition.ISOLATION_REPEATABLE_READ
}
)

val users = Flux.defer {
Flux.create {
it.next(1)
it.next(2)
it.next(3)
it.next(4)
it.complete()
}
.parallel(4)
.runOn(Schedulers.parallel())
.flatMap {
databaseClient.sql(
"""
select * from …
""".trimIndent())
.map { row, _ -> EntityConverter().convert(row) }
.all()
.reduce(EntityReducer())
}
}.`as`(transactionalOperator::transactional)
```

I’m aware that Postgres doesn’t support cursor(portal) multiplexing so its
impossible to fetch data for two selects simultaneously but should I make
use of selects pipelining when I want to make the most of the DB? I mean to
say does a single Postgres backend employs concurrent processing and its
possible for a DB to transmit results for a query #2 (see the traffic) and
concurrently parse/rewrite/plan/optimize the subsequent (pipelined) queries
(issued from the same transaction). Neither chapter 55 nor 52 expand on it
(or it’s hard to spot at least).

Thanks!

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message PG Doc comments form 2024-01-25 04:58:14 ERROR: plpython3u
Previous Message David G. Johnston 2024-01-24 23:04:51 Re: SQL command : ALTER DATABASE OWNER TO