4 ms·

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

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

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