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

Поиск
Список
Период
Сортировка
От PG Doc comments form
Тема It would be nice to clarify is there any point in select queries pipelining
Дата
Msg-id 170615801656.662.17755050278063339844@wrigleys.postgresql.org
обсуждение исходный текст
Ответы Re: It would be nice to clarify is there any point in select queries pipelining  (Laurenz Albe <laurenz.albe@cybertec.at>)
Список 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!

В списке pgsql-docs по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: SQL command : ALTER DATABASE OWNER TO
Следующее
От: PG Doc comments form
Дата:
Сообщение: ERROR: plpython3u