From: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
---|---|
To: | pgsql-general general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: efficiently migrating 'old' data from one table to another |
Date: | 2017-01-13 15:42:20 |
Message-ID: | 177D48CC-11B5-408C-B792-00AC6D6B8158@2xlp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Jan 12, 2017, at 5:52 PM, Merlin Moncure wrote:
> On Thu, Jan 12, 2017 at 2:19 PM, btober(at)computer(dot)org
> <btober(at)broadstripe(dot)net> wrote:
>>
>> Review manual section 7.8.2. Data-Modifying Statements in WITH
>>
>>
>> https://www.postgresql.org/docs/9.6/static/queries-with.html
>
> this.
>
> with data as (delete from foo where ... returning * ) insert into
> foo_backup select * from data;
Thanks, btober and merlin. that's exactly what i want.
On Jan 12, 2017, at 4:45 PM, Adrian Klaver wrote:
> Maybe I am missing something, but why do the UPDATE?
> Why not?:
> ...
> With an index on record_timestamp.
That's actually the production deployment that we're trying to optimize. Depending on the size of the table (rows, width) it performs "less than great", even with the index on record_timestamp.
The UPDATE actually worked faster in most situations. I honestly don't know why (the only thing that makes sense to me is server-load)... but the update + bool test ended up being (much) faster than the timestamp comparison.
From | Date | Subject | |
---|---|---|---|
Next Message | Denisa Cirstescu | 2017-01-13 15:45:39 | COPY value TO STDOUT |
Previous Message | Melvin Davidson | 2017-01-13 15:42:07 | Re: temporarily disable autovacuum on a database or server ? |