Thread: Best possible way to insert and get returned ids

Best possible way to insert and get returned ids

From
Jason Dictos
Date:

Question:

 

Is an INSERT command with a SELECT statement in the RETURNING * parameter faster than say an INSERT and then a SELECT? Does the RETURNING * parameter simply amount to a normal SELECT command on the added rows? We need to basically insert a lot of rows as fast as possible, and get the ids that were added.  The number of rows we are inserting is dynamic and is not of fixed length.

 

Thanks,

-Jason

 

----------------------------------
Check out the Barracuda Spam & Virus Firewall - offering the fastest virus & malware protection in the industry: www.barracudanetworks.com/spam

Re: Best possible way to insert and get returned ids

From
Scott Marlowe
Date:
On Mon, Nov 23, 2009 at 1:53 PM, Jason Dictos <jdictos@barracuda.com> wrote:
> Question:
>
> Is an INSERT command with a SELECT statement in the RETURNING * parameter
> faster than say an INSERT and then a SELECT? Does the RETURNING * parameter
> simply amount to a normal SELECT command on the added rows? We need to
> basically insert a lot of rows as fast as possible, and get the ids that
> were added.  The number of rows we are inserting is dynamic and is not of
> fixed length.

Well, if you do an insert, then a select, how can you tell, with that
select, which rows you just inserted?  how can you be sure they're not
somebody elses?

Insert returning is fantastic for this type of thing.  The beauty of
it is that it returns a SET if you insert multiple rows.  And, if
you've got two insert threads running, and one inserts to a sequence a
set of rows with pk values of 10,11,13,15,18,20 while another thread
inserts to the same table and creates a set of rows with pk values of
12,14,16,17,19 then those are the two sets you'll get back with
returning.

Re: Best possible way to insert and get returned ids

From
Robert Haas
Date:
On Mon, Nov 23, 2009 at 3:53 PM, Jason Dictos <jdictos@barracuda.com> wrote:
> Is an INSERT command with a SELECT statement in the RETURNING * parameter
> faster than say an INSERT and then a SELECT? Does the RETURNING * parameter
> simply amount to a normal SELECT command on the added rows? We need to
> basically insert a lot of rows as fast as possible, and get the ids that
> were added.  The number of rows we are inserting is dynamic and is not of
> fixed length.

With INSERT ... RETURNING, you only make one trip to the heap, so I
would expect it to be faster.  Plus, of course, it means you don't
have to worry about writing a WHERE clause that can identify the
row(s) you just added.  It sounds like the right tool for your use
case.

...Robert