Thread: assignment vs SELECT INTO
I found out today that direct assignment to a composite type is (at least in my test) about 70% faster than setting it via SELECT INTO. That seems like an enormous difference in speed, which I haven't really been able to account for. Test case: andrew=# \d abc Table "public.abc" Column | Type | Modifiers --------+---------+----------- x | text | y | text | z | integer | andrew=# do $x$ declare r abc; begin for i in 1 .. 10000000 loop select 'a','b',i into r.x,r.y,r.z; end loop; end; $x$; DO Time: 63731.434 ms andrew=# do $x$ declare r abc; begin for i in 1 .. 10000000 loop r := ('a','b',i); end loop; end; $x$; DO Time: 18744.151 ms Is it simply because the SELECT is in effect three assignments, so it takes nearly 3 times as long? cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes: > andrew=# do $x$ declare r abc; begin for i in 1 .. 10000000 loop > select 'a','b',i into r.x,r.y,r.z; end loop; end; $x$; > DO > Time: 63731.434 ms > andrew=# do $x$ declare r abc; begin for i in 1 .. 10000000 loop r > := ('a','b',i); end loop; end; $x$; > DO > Time: 18744.151 ms > Is it simply because the SELECT is in effect three assignments, so it > takes nearly 3 times as long? I think it's more likely that the second example is treated as a "simple expression" so it has less overhead than a SELECT. regards, tom lane
On Mon, Nov 3, 2014 at 6:00 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
andrew=# do $x$ declare r abc; begin for i in 1 .. 10000000 loop
select 'a','b',i into r.x,r.y,r.z; end loop; end; $x$;
DO
Time: 63731.434 ms
andrew=# do $x$ declare r abc; begin for i in 1 .. 10000000 loop r
:= ('a','b',i); end loop; end; $x$;
DO
Time: 18744.151 ms
Is it simply because the SELECT is in effect three assignments, so it takes nearly 3 times as long?
I don't think so, because this take pretty much the same time:
SELECT ('a','b',i) INTO r;
SELECT ('a','b',i) INTO r;
Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres
On 11/03/2014 03:24 PM, Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: >> andrew=# do $x$ declare r abc; begin for i in 1 .. 10000000 loop >> select 'a','b',i into r.x,r.y,r.z; end loop; end; $x$; >> DO >> Time: 63731.434 ms >> andrew=# do $x$ declare r abc; begin for i in 1 .. 10000000 loop r >> := ('a','b',i); end loop; end; $x$; >> DO >> Time: 18744.151 ms >> Is it simply because the SELECT is in effect three assignments, so it >> takes nearly 3 times as long? > I think it's more likely that the second example is treated as a "simple > expression" so it has less overhead than a SELECT. > > Well, I accidetally left out this case: andrew=# do $x$ declare r abc; begin for i in 1 .. 10000000 loop select row('a','b',i) into r; end loop; end; $x$; DO Time: 81919.721 ms which is slower still. cheers andrew