From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
---|---|
To: | pgsql-perform <pgsql-performance(at)postgresql(dot)org> |
Subject: | assignment vs SELECT INTO |
Date: | 2014-11-03 20:00:14 |
Message-ID: | 5457DECE.3070302@dunslane.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-11-03 20:24:05 | Re: assignment vs SELECT INTO |
Previous Message | Felipe Santos | 2014-11-03 14:34:44 | Re: Replication Lag Causes |