Re: Long running INSERT+SELECT query

2018-04-27 Thread Tim Cross
Steven Lembark writes: > On Fri, 27 Apr 2018 19:38:15 +0300 > Vitaliy Garnashevich wrote: > >> We're going to try using "SELECT 1 FROM table FOR KEY SHARE" for each of >> the tables, which are referenced by results, before running the big >> query. That should be up to a million of rows in to

Re: Long running INSERT+SELECT query

2018-04-27 Thread Steven Lembark
On Fri, 27 Apr 2018 19:38:15 +0300 Vitaliy Garnashevich wrote: > We're going to try using "SELECT 1 FROM table FOR KEY SHARE" for each of > the tables, which are referenced by results, before running the big > query. That should be up to a million of rows in total. It will probably > not cover

Re: Long running INSERT+SELECT query

2018-04-27 Thread Vitaliy Garnashevich
Everybody thanks for the suggestions! We're going to try using "SELECT 1 FROM table FOR KEY SHARE" for each of the tables, which are referenced by results, before running the big query. That should be up to a million of rows in total. It will probably not cover the case when a record is INSERT

Re: Long running INSERT+SELECT query

2018-04-27 Thread Steven Lembark
On Thu, 26 Apr 2018 23:32:33 +0300 Vitaliy Garnashevich wrote: > The aggregated table has hundreds of millions of rows, and the query > runs for many hours (which is one of the reasons why it's better not > to fail). I really doubt that row level locking would work. That > would be a lot of RAM

Re: Long running INSERT+SELECT query

2018-04-27 Thread Steven Lembark
On Thu, 26 Apr 2018 21:08:01 +0300 Vitaliy Garnashevich wrote: >     INSERT INTO cmdb_sp_usage_history >   (created_by, updated_by, created_on, updated_on, mod_count, >   summary_on, quarter, product, used_from, "user", >   keystrokes, minutes_in_use, times_started, avg_keystrokes

Re: Long running INSERT+SELECT query

2018-04-27 Thread Steven Lembark
On Thu, 26 Apr 2018 19:13:17 +0300 Vitaliy Garnashevich wrote: > We're trying to populate a table with aggregated data from other > tables. For that we're running a huge INSERT+SELECT query which joins > several tables, aggregates values, and then inserts the results into > another table. The pro

Re: Long running INSERT+SELECT query

2018-04-26 Thread Adrian Klaver
On 04/26/2018 01:32 PM, Vitaliy Garnashevich wrote: Have not worked through all of the above, but a first draft suggestion: Move the SELECT minus the aggregation functions into a sub-query that uses FOR UPDATE. Then do the aggregation on the results of the sub-query. The aggregated table h

Re: Long running INSERT+SELECT query

2018-04-26 Thread Alvaro Herrera
Vitaliy Garnashevich wrote: > > > Have not worked through all of the above, but a first draft suggestion: > > > > Move the SELECT minus the aggregation functions into a sub-query that > > uses FOR UPDATE. Then do the aggregation on the results of the > > sub-query. > > The aggregated table has h

Re: Long running INSERT+SELECT query

2018-04-26 Thread David G. Johnston
On Thu, Apr 26, 2018 at 1:32 PM, Vitaliy Garnashevich < vgarnashev...@gmail.com> wrote: > The aggregated table has hundreds of millions of rows, and the query runs > for many hours (which is one of the reasons why it's better not to fail) > ​[...]​ > Maybe this could be solved by calculating res

Re: Long running INSERT+SELECT query

2018-04-26 Thread Vitaliy Garnashevich
Have not worked through all of the above, but a first draft suggestion: Move the SELECT minus the aggregation functions into a sub-query that uses FOR UPDATE. Then do the aggregation on the results of the sub-query. The aggregated table has hundreds of millions of rows, and the query runs

Re: Long running INSERT+SELECT query

2018-04-26 Thread Adrian Klaver
On 04/26/2018 11:08 AM, Vitaliy Garnashevich wrote: Without the query we are flying blind, so suggestions will have a ? Here is one such query:     INSERT INTO cmdb_sp_usage_history   (created_by, updated_by, created_on, updated_on, mod_count,   summary_on, quarter, product, used_

Re: Long running INSERT+SELECT query

2018-04-26 Thread Vitaliy Garnashevich
Without the query we are flying blind, so suggestions will have a ? Here is one such query:     INSERT INTO cmdb_sp_usage_history   (created_by, updated_by, created_on, updated_on, mod_count,   summary_on, quarter, product, used_from, "user",   keystrokes, minutes_in_use, times_st

Re: Long running INSERT+SELECT query

2018-04-26 Thread Adrian Klaver
On 04/26/2018 10:45 AM, Vitaliy Garnashevich wrote: SELECT .. FOR UPDATE?: https://www.postgresql.org/docs/10/static/sql-select.html#SQL-FOR-UPDATE-SHARE https://www.postgresql.org/docs/10/static/explicit-locking.html#LOCKING-ROWS It says: "Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR

Re: Long running INSERT+SELECT query

2018-04-26 Thread Vitaliy Garnashevich
SELECT .. FOR UPDATE?: https://www.postgresql.org/docs/10/static/sql-select.html#SQL-FOR-UPDATE-SHARE https://www.postgresql.org/docs/10/static/explicit-locking.html#LOCKING-ROWS It says: "Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified with GR

Re: Long running INSERT+SELECT query

2018-04-26 Thread Adrian Klaver
On 04/26/2018 09:13 AM, Vitaliy Garnashevich wrote: Hi, We're trying to populate a table with aggregated data from other tables. For that we're running a huge INSERT+SELECT query which joins several tables, aggregates values, and then inserts the results into another table. The problem we're

Long running INSERT+SELECT query

2018-04-26 Thread Vitaliy Garnashevich
Hi, We're trying to populate a table with aggregated data from other tables. For that we're running a huge INSERT+SELECT query which joins several tables, aggregates values, and then inserts the results into another table. The problem we're facing is that while the query is running , some rec