4 ms·

As a follow-up. Here's what Postgres' "generate_series" function looks like with pgx: https://github.com/zombodb/pgx/blob/0803bd3aa2f8ef2bd80d2e039bed016fd396

As a follow-up. Here's what Postgres' "generate_series" function looks like with pgx: https://github.com/zombodb/pgx/blob/0803bd3aa2f8ef2bd80d2e039bed016fd3965b73/pgx-examples/srf/src/lib.rs#L11 https://github.com/zombodb/pgx/blob/0803bd3aa2f8ef2bd80d2e03... And here's what you'd have to do to implement it in C: https://github.com/postgres/postgres/blob/dad75eb4a8d5835ecc795d7a7978e7702e4d5912/src/backend/utils/adt/int8.c#L1426 https://github.com/postgres/postgres/blob/dad75eb4a8d5835ecc...

zombodb 6y ago
One more follow-up... The top one is pgx, the bottom is Postgres. So there's a little room for improvement here with pgx, but that's okay for a v0.0.3 release. test=# select count(*) from srf.generate_series(1, 10000000); Time: 1552.115 ms (00:01.552) test=# select count(*) from generate_series(1, 10000000); Time: 1406.357 ms (00:01.406)
- anarazel 6y ago
  The largest part of the time executing the above query isn't inside the function, so this isn't that a material comparison. The reason for that is that SRFs in FROM to be materialized into a tuplestore, which isn't free: postgres[607045][1]=# SELECT count(*) FROM generate_series(1, 10000000); ┌──────────┐ │ count │ ├──────────┤ │ 10000000 │ └──────────┘ (1 row) Time: 1249.224 ms (00:01.249) postgres[607045][1]=# SELECT count(*) FROM (SELECT generate_series(1, 10000000)) s; ┌──────────┐ │ count │ ├──────────┤ │ 10000000 │ └──────────┘ (1 row) Time: 460.206 ms For mostly historical reasons SRFs in the target list can use the non-materializing SRF query protocol, but SRFs in the FROM list can't. Any chance you could show the timings for the pgx version of the second query?
  - zombodb 6y ago
    Sure! Top is Postgres, bottom is pgx, after running each 5 times... test=# SELECT count(*) FROM (SELECT generate_series(1, 10000000)) s; count ---------- 10000000 (1 row) Time: 399.630 ms test=# SELECT count(*) FROM (SELECT srf.generate_series(1, 10000000)) s; count ---------- 10000000 (1 row) Time: 478.194 ms Thanks for the reply. I'm not surprised there's room for optimization in pgx, especially in the Iterator-->SRF path. edit: PS, thanks for the idea of putting what I assume is the backend pid in psql's prompt. I need to go figure out how to do that right now! edit edit: hmm, I guess that's not the PID.
    - anarazel 6y ago
      Thanks for the update. > edit: PS, thanks for the idea of putting what I assume is the backend pid in psql's prompt. I need to go figure out how to do that right now! Here's my ~/.psqlrc: andres@awork3:~/src/postgresql$ cat ~/.psqlrc \set QUIET 1 \pset pager always \set VERBOSITY verbose \pset null '(null)' \set COMP_KEYWORD_CASE upper \pset linestyle unicode \pset border 2 \set PROMPT1 '%/[%p][%l]%x%R%# ' \set PROMPT2 '%/[%p][%l]%x%R%# ' \set PROMPT3 'c:%/[%p][%l]%x%R%# ' \set HISTCONTROL ignoredups \set HISTSIZE 100000 \timing on \set QUIET 0
db48x 6y ago
That is certainly nicer.
dwaltrip 6y ago
In the function definition of `generate_series`, what is going on with the `default!` macro? That isn't some sort of way of actually getting real default param values is it? I thought that wasn't possible in Rust.

zombodb 6y ago

One more follow-up... The top one is pgx, the bottom is Postgres. So there's a little room for improvement here with pgx, but that's okay for a v0.0.3 release. test=# select count(*) from srf.generate_series(1, 10000000); Time: 1552.115 ms (00:01.552) test=# select count(*) from generate_series(1, 10000000); Time: 1406.357 ms (00:01.406)

anarazel 6y ago
The largest part of the time executing the above query isn't inside the function, so this isn't that a material comparison. The reason for that is that SRFs in FROM to be materialized into a tuplestore, which isn't free: postgres[607045][1]=# SELECT count(*) FROM generate_series(1, 10000000); ┌──────────┐ │ count │ ├──────────┤ │ 10000000 │ └──────────┘ (1 row) Time: 1249.224 ms (00:01.249) postgres[607045][1]=# SELECT count(*) FROM (SELECT generate_series(1, 10000000)) s; ┌──────────┐ │ count │ ├──────────┤ │ 10000000 │ └──────────┘ (1 row) Time: 460.206 ms For mostly historical reasons SRFs in the target list can use the non-materializing SRF query protocol, but SRFs in the FROM list can't. Any chance you could show the timings for the pgx version of the second query?
- zombodb 6y ago
  Sure! Top is Postgres, bottom is pgx, after running each 5 times... test=# SELECT count(*) FROM (SELECT generate_series(1, 10000000)) s; count ---------- 10000000 (1 row) Time: 399.630 ms test=# SELECT count(*) FROM (SELECT srf.generate_series(1, 10000000)) s; count ---------- 10000000 (1 row) Time: 478.194 ms Thanks for the reply. I'm not surprised there's room for optimization in pgx, especially in the Iterator-->SRF path. edit: PS, thanks for the idea of putting what I assume is the backend pid in psql's prompt. I need to go figure out how to do that right now! edit edit: hmm, I guess that's not the PID.
  - anarazel 6y ago
    Thanks for the update. > edit: PS, thanks for the idea of putting what I assume is the backend pid in psql's prompt. I need to go figure out how to do that right now! Here's my ~/.psqlrc: andres@awork3:~/src/postgresql$ cat ~/.psqlrc \set QUIET 1 \pset pager always \set VERBOSITY verbose \pset null '(null)' \set COMP_KEYWORD_CASE upper \pset linestyle unicode \pset border 2 \set PROMPT1 '%/[%p][%l]%x%R%# ' \set PROMPT2 '%/[%p][%l]%x%R%# ' \set PROMPT3 'c:%/[%p][%l]%x%R%# ' \set HISTCONTROL ignoredups \set HISTSIZE 100000 \timing on \set QUIET 0

db48x 6y ago

That is certainly nicer.

dwaltrip 6y ago

In the function definition of `generate_series`, what is going on with the `default!` macro? That isn't some sort of way of actually getting real default param values is it? I thought that wasn't possible in Rust.