3 ms·
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
by 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 agoSure! 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 agoThanks 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