| 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: | Whole Thread | Raw Message | 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 |