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

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 > <mailto:jim.na...@bluetreble.com>> wrote: >> >> On 4/14/15 1:28 PM, Pawel Veselov wrote: >> >> >&

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 >> th

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 qu

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 (

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 o

[GENERAL] bigserial continuity safety

Hi. If I have a table created as: CREATE TABLE xq_agr ( idBIGSERIAL PRIMARY KEY, node text not null ); and that multiple applications insert into. The applications never explicitly specify the value for 'id'. Is it safe to, on a single connection, do: - open tra

Re: [GENERAL] Improving performance of merging data between tables

Sorry, it took me a while to respond, but I re-factored all of this process to suggestions. On Wed, Jan 7, 2015 at 7:49 PM, Maxim Boguk wrote: > > On Wed, Jan 7, 2015 at 8:49 PM, Pawel Veselov > wrote: >> >> PS: your setup look pretty complicated and hard to analyze w

Re: [GENERAL] Casting hstore to json

Adrian, On Thu, Jan 15, 2015 at 9:09 PM, Adrian Klaver wrote: > On 01/15/2015 07:59 PM, Pawel Veselov wrote: > >> Hi. >> >> I'm trying to cast hstore to json, but I don't seem to be getting a json >> object from hstore key/value pairs. >> >&g

[GENERAL] Casting hstore to json

Hi. I'm trying to cast hstore to json, but I don't seem to be getting a json object from hstore key/value pairs. 9.3 documentation says: *Note:* The hstore extension has a cast from hstore to json, so that converted hstore values are represe

Re: [GENERAL] min/max performance inequality.

Thanks Jeff (and Tom) On Wed, Jan 7, 2015 at 3:34 PM, Jeff Janes wrote: > On Wed, Jan 7, 2015 at 3:00 PM, Pawel Veselov > wrote: > >> Hi. >> >> I was wondering how come there is such a drastic difference between >> finding max and min. Seems like "inde

[GENERAL] min/max performance inequality.

Hi. I was wondering how come there is such a drastic difference between finding max and min. Seems like "index scan backwards" is really bad... The table is freshly re-indexed just in case. I added a count(*) in there, forcing the seq scan, and it's even better than the backwards index scan... db

Re: [GENERAL] Improving performance of merging data between tables

On Tue, Dec 30, 2014 at 7:25 PM, Maxim Boguk wrote: > > On Wed, Dec 31, 2014 at 11:10 AM, Pawel Veselov > wrote >> >> >> [skipped] >> >> 2) try pg_stat_statements, setting "pg_stat_statements.track = all". see: >>>> http://www.pos

Re: [GENERAL] Improving performance of merging data between tables

On Mon, Dec 29, 2014 at 9:29 PM, Pawel Veselov wrote: [skipped] >>> 1) How do I find out what exactly is consuming the CPU in a PL/pgSQL >>> function? All I see is that the calls to merge_all() function take long >>> time, and the CPU is high while this is going on

Re: [GENERAL] Improving performance of merging data between tables

Andy, thanks for looking into this. On Mon, Dec 29, 2014 at 9:00 AM, Andy Colson wrote: > On 12/28/2014 3:49 PM, Pawel Veselov wrote: > >> Hi. >> >> I was wondering if anybody would have any ideas on how to improve >> certain operations that we are having. >>

[GENERAL] Improving performance of merging data between tables

Hi. I was wondering if anybody would have any ideas on how to improve certain operations that we are having. PostgreSQL 9.3.3. Table layout: main_table: PK, N key columns, M data columns. The key columns are bound in a unique key together. PK is pure sequence number. There are few separate non-u

[GENERAL] documentation for lo_* functions

Hi. Where is the proper documentation for lo_* functions (e.g. lo_open) that are available as SQL functions? I see libpq functions documented in /static/lo-interfaces.html, but not the SQL ones (from pg_catalog). Thank you, Pawel.

[GENERAL] incremental digest (and other) functions?

Hi. I was looking into a need of doing SHA on contents of a "large object", and it seems that the only digest (and other) functions provided by pgcrypto produce output based on a single string on an input, without being able to accumulate for multiple data blocks. It would be nice to see those, IM

[GENERAL] ORDER BY expression required in SELECT if DISTINCT

Hi. Anything I can do about this? Seems that the problem is that the query is a prepared statement, even if the parameters ($1 and $6) are the same. ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list at character 493 STATEMENT: SELECT DISTINCT t0.package_name, ts_rank(t

Re: [GENERAL] Understanding streaming replication

On Mon, Nov 12, 2012 at 10:11 AM, Pawel Veselov wrote: > > On Mon, Nov 12, 2012 at 1:36 AM, Albe Laurenz wrote: > >> I'll try to answer the questions I can. >> > > Thank you! > > >> Pawel Veselov wrote: >> > I've been struggling wi

Re: [GENERAL] Understanding streaming replication

On Mon, Nov 12, 2012 at 1:36 AM, Albe Laurenz wrote: > I'll try to answer the questions I can. > Thank you! > Pawel Veselov wrote: > > I've been struggling with understanding all the necessary pieces for > streaming replication. So I put > > down the pieces a

[GENERAL] Understanding streaming replication

Hi. I've been struggling with understanding all the necessary pieces for streaming replication. So I put down the pieces as I did understand them, and would appreciate if you guys could point out any of the stuff I understood or have done wrong. The set up is pgpool + streaming replication + hot

Re: [GENERAL] index update

On Tue, Jul 17, 2012 at 9:18 PM, Craig Ringer wrote: > On 07/18/2012 12:02 PM, Pawel Veselov wrote: > > Hi. > > If I have a lot (10k) tables, and each table has a btree index, and all > the tables are being constantly inserted into, would all the indexes have > to be

[GENERAL] index update

Hi. If I have a lot (10k) tables, and each table has a btree index, and all the tables are being constantly inserted into, would all the indexes have to be in memory, and would effectively start fighting for space? Thank you, Pawel.

[GENERAL] errors with high connections rate

Hi. -- problem 1 -- I have an application, using libpq, connecting to postgres 9.1.3 (Amazon AMI distro). The application writes data at a high rate (at this point it's 500 transaction per second), using multiple threads (at this point it's 800). These are "worker" threads, that receive "message

Re: [GENERAL] 31.11. Notice Processing - associating notices with calls

On Mon, Jun 25, 2012 at 8:07 AM, Tom Lane wrote: > Pawel Veselov writes: > > What's the best way to "associate" an incoming notice with the statement > > that resulted in generating it? > > Um ... the first issue with this problem statement is the assumptio

[GENERAL] 31.11. Notice Processing - associating notices with calls

Hi. What's the best way to "associate" an incoming notice with the statement that resulted in generating it? Notice operate on PGResult objects, but these objects only become available after the statement call is made. Should I have a global (or thread global, since my app is MT) "flag" that would