Re: [GENERAL] Help with slow table update

On 4/19/15 9:53 PM, Tim Uckun wrote: On Sat, Apr 18, 2015 at 10:24 AM, Pawel Veselov mailto:pawel.vese...@gmail.com>> wrote: I found some dangling prepared transactions How do you find and remove these? SELECT * FROM pg_prepared_xacts; ROLLBACK PREPARED xid; -- Jim Nasby, Data Architec

Re: [GENERAL] Help with slow table update

On Sat, Apr 18, 2015 at 10:24 AM, Pawel Veselov wrote: > I found some dangling prepared transactions How do you find and remove these?

Re: [GENERAL] Help with slow table update

> > [skipped] > > >> But remember that if you update or delete a row, removing it from an >>> index, the data will stay in that index until vacuum comes along. >>> >>> Also, there's no point in doing a REINDEX after a VACUUM FULL; >>> vacuum full rebuilds all the indexes for you. >

Re: [GENERAL] Help with slow table update

> > [skipped] > > > > This is where using sets becomes really tedious, as Postgres severely > lacks an upsert-like statement. > > I don't think there are joins allowed in UPDATE statement, so I will need > to use WITH query, right? > > Also, I'm not sure how LEFT JOIN will help me isolate and inser

Re: [GENERAL] Help with slow table update

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Pawel Veselov Sent: Tuesday, April 14, 2015 8:01 PM To: Jim Nasby Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Help with slow table update [skipped] This is where using sets becomes

Re: [GENERAL] Help with slow table update

On Tue, Apr 14, 2015 at 3:29 PM, Jim Nasby wrote: > On 4/14/15 4:44 PM, Pawel Veselov wrote: > >> On Tue, Apr 14, 2015 at 1:15 PM, Jim Nasby > > wrote: >> >> On 4/14/15 1:28 PM, Pawel Veselov wrote: >> >> >> I wonder if what I need to do, considering t

Re: [GENERAL] Help with slow table update

On 4/14/15 4:44 PM, Pawel Veselov wrote: On Tue, Apr 14, 2015 at 1:15 PM, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote: On 4/14/15 1:28 PM, Pawel Veselov wrote: I wonder if what I need to do, considering that I update a lot of "the same" rows as I process this

Re: [GENERAL] Help with slow table update

On Tue, Apr 14, 2015 at 1:15 PM, Jim Nasby wrote: > On 4/14/15 1:28 PM, Pawel Veselov wrote: > >> >> I wonder if what I need to do, considering that I update a lot of "the >> same" rows as I process this queue, is to create a temp table, update >> the rows there, and then update the actual tables

Re: [GENERAL] Help with slow table update

On 4/14/15 1:28 PM, Pawel Veselov wrote: I wonder if what I need to do, considering that I update a lot of "the same" rows as I process this queue, is to create a temp table, update the rows there, and then update the actual tables once at the end... That's what I'd do. The other option would

Re: [GENERAL] Help with slow table update

On Mon, Apr 13, 2015 at 7:37 PM, Jim Nasby wrote: > On 4/13/15 7:01 PM, Pawel Veselov wrote: > >> Cursors tend to make things slow. Avoid them if you can. >> >> >> Is there an alternative to iterating over a number of rows, where a >> direct update query is not an option? >> >> I really doubt

Re: [GENERAL] Help with slow table update

On Mon, Apr 13, 2015 at 6:03 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Apr 13, 2015 at 5:01 PM, Pawel Veselov > wrote: > >> >> r_agrio_hourly - "good", r_agrio_total - "bad". >> >> Update on r_agrio_hourly (cost=0.42..970.32 rows=250 width=329) (actual >> time=2.248..

Re: [GENERAL] Help with slow table update

On 4/13/15 7:01 PM, Pawel Veselov wrote: Cursors tend to make things slow. Avoid them if you can. Is there an alternative to iterating over a number of rows, where a direct update query is not an option? I really doubt that either the actual processing logic, including use of types has any

Re: [GENERAL] Help with slow table update

On Mon, Apr 13, 2015 at 5:01 PM, Pawel Veselov wrote: > > r_agrio_hourly - "good", r_agrio_total - "bad". > > Update on r_agrio_hourly (cost=0.42..970.32 rows=250 width=329) (actual > time=2.248..2.248 rows=0 loops=1) >-> Index Scan using u_r_agrio_hourly on r_agrio_hourly > (cost=0.42..9

Re: [GENERAL] Help with slow table update

On Sun, Apr 12, 2015 at 5:40 PM, Jim Nasby wrote: > On 4/9/15 6:18 PM, Pawel Veselov wrote: > >> Hi. >> >> I have a plpgsql procedure that updates a few similar tables. >> for some reason, updates on one of the tables take a lot longer the >> updates on the other ones. The difference is, say, 7 s

Re: [GENERAL] Help with slow table update

On 4/9/15 6:18 PM, Pawel Veselov wrote: Hi. I have a plpgsql procedure that updates a few similar tables. for some reason, updates on one of the tables take a lot longer the updates on the other ones. The difference is, say, 7 seconds vs. 80 milliseconds. the procedure uses cursors and record v