subject:"\[PERFORM\] insert"

On Wed, Jul 15, 2015 at 12:16 PM, Robert DiFalco robert.difa...@gmail.com
wrote:

 First off I apologize if this is question has been beaten to death. I've
 looked around for a simple answer and could not find one.

 Given a database that will not have it's PKEY or indices modified, is it
 generally faster to INSERT or UPDATE data. And if there is a performance
 difference is it substantial?

 I have a situation where I can easily do one or the other to the same
 effect. For example, I have a journaling schema with a limited number of
 states for an entry. Currently each state is it's own table so I just
 insert them as they occur. But I could easily have a single entry table
 where the row is updated with column information for states (after the
 entry's initial insertion).

 Not a big deal but since it's so easy for me to take either approach I was
 wondering if one was more efficient (for a large DB) than another.


There is HOT (heap only tuple?) optimization that can occur if only
non-indexed data is altered.  I do not recall the specifics.

Dave

Re: [PERFORM] Insert vs Update

On Wednesday, July 15, 2015, Robert DiFalco robert.difa...@gmail.com
wrote:

 First off I apologize if this is question has been beaten to death. I've
 looked around for a simple answer and could not find one.

 Given a database that will not have it's PKEY or indices modified, is it
 generally faster to INSERT or UPDATE data. And if there is a performance
 difference is it substantial?


This seems odd.  If you have an option to update but choose to insert what
becomes of the other record?

Re: [PERFORM] Insert vs Update

2015-07-15 Thread Michael Nolan

I

On Wed, Jul 15, 2015 at 12:16 PM, Robert DiFalco robert.difa...@gmail.com
wrote:

 First off I apologize if this is question has been beaten to death. I've
 looked around for a simple answer and could not find one.

 Given a database that will not have it's PKEY or indices modified, is it
 generally faster to INSERT or UPDATE data. And if there is a performance
 difference is it substantial?

 I have a situation where I can easily do one or the other to the same
 effect. For example, I have a journaling schema with a limited number of
 states for an entry. Currently each state is it's own table so I just
 insert them as they occur. But I could easily have a single entry table
 where the row is updated with column information for states (after the
 entry's initial insertion).

 Not a big deal but since it's so easy for me to take either approach I was
 wondering if one was more efficient (for a large DB) than another.

 Thanks


If you think of an update as a delete-insert operation (glossing over the
fine points of what has to be done for ACID), it seems pretty clear that an
update involves more work than an insert.  Measuring that impact on
performance is probably a bit more challenging, because it's going to be
dependent on the specific table and the contents of the row, among other
things.
--
Mike Nolan
no...@tssi.com

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 11:15 AM, David G. Johnston 
david.g.johns...@gmail.com wrote:


 Yes, you are trying to choose between a bunch of one-to-one (optional)
 relationships versus adding additional columns to a table all of which can
 be null.

 I'd argue that neither option is normal (in the DB normalization sense).

 CREATE TABLE meal (meal_id bigserial)
 CREATE TABLE meal_event_type (meal_event_id bigserial)
 CREATE TABLE meal_event (meal_id bigint, meal_event_id bigint, occurred_at
 timestamptz)

 So now the decision is one of how to denormalize.  materialzed views and
 two ways to do so.  The specific solution would depend in part on the final
 application queries that you need to write.

 If you do want to model the de-normalized form, which I would likely be
 tempted to do given a fixed set of events that do not require additional
 related attributes, would be to place the few event timestamps on the main
 table and UPDATE them to non-null.

 In the normal form you will likely find partial indexes to be quite useful.

 David J.
 


Thanks David, my example was a big simplification, but I appreciate your
guidance. The different event types have differing amounts of related data.
Query speed on this schema is not important, it's really the write speed
that matters. So I was just wondering given the INSERT or UPDATE approach
(with no indexed data being changed) if one is likely to be substantially
faster than the other.

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 3:16 PM, Robert DiFalco robert.difa...@gmail.com
wrote:

 The different event types have differing amounts of related data.


On this basis alone I would select the multiple-table version as my
baseline and only consider something different if the performance of this
was insufficient and I could prove that an alternative arrangement was more
performant.

A single optional date with meta-data embedded in the column name

is usually workable but if you then have a bunch of other columns with
name like:

preparation_date, preparation_col1, preparation_col2, consumed_col1,
consumed_col2, consumed_date


I would find that to be undesirable.

You may be able to put Table Inheritance to good use here...

I do not know (but doubt) if HOT optimization works when going from NULL to
non-NULL since the former is stored in a bitmap while the later occupies
normal relation space and thus the update would likely end up writing an
entirely new record upon each event category recording.

David J.

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 12:32 PM, David G. Johnston 
david.g.johns...@gmail.com wrote:


 You may be able to put Table Inheritance to good use here...

 I do not know (but doubt) if HOT optimization works when going from NULL
 to non-NULL since the former is stored in a bitmap while the later occupies
 normal relation space and thus the update would likely end up writing an
 entirely new record upon each event category recording.

 David J.



Thanks!

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 10:33 AM, David G. Johnston 
david.g.johns...@gmail.com wrote:

 On Wednesday, July 15, 2015, Robert DiFalco robert.difa...@gmail.com
 wrote:

 First off I apologize if this is question has been beaten to death. I've
 looked around for a simple answer and could not find one.

 Given a database that will not have it's PKEY or indices modified, is it
 generally faster to INSERT or UPDATE data. And if there is a performance
 difference is it substantial?


 This seems odd.  If you have an option to update but choose to insert what
 becomes of the other record?



Consider the two pseudo-schemas, I'm just making this up for example
purposes:

SCHEMA A
=
meal(id SEQUENCE,user_id, started DEFAULT NOW())
meal_prepared(ref_meal_id, prepared DEFAULT NOW())
meal_abandoned(ref_meal_id, abandoned ...)
meal_consumed(ref_meal_id, consumed ...)
etc.

Then in response to different meal events you always have an insert.

aMealId = INSERT INTO meal(user_id) VALUES (aUserId);

When preparation starts:

INSERT INTO meal_prepared(ref_meal_id) VALUES (aMealId);

And so on for each event.

Compare that to this:

SCHEMA B
=
meal_event(id, started, prepared, abandoned, consumed, ...)

The start of the meal is an INSERT:

aMealId = INSERT INTO meal_event(user_id, started) VALUES (aUserId, NOW());

When preparation starts:

UPDATE meal_event SET prepared = NOW() WHERE id = aMealId;

And so on.

Basically the same data, in one case you always do inserts and add new
tables for new events. In the other case you only insert once and then
update for each state, then you add columns if you have new states.

As I said this is just an example. But in SCHEMA A you have only inserts,
lots of tables and in SCHEMA B you have a lot of updates and a lot of
possibly NULL columns if certain events don't occur.

Is that more clear?

R.

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 1:56 PM, Robert DiFalco robert.difa...@gmail.com
wrote:



 On Wed, Jul 15, 2015 at 10:33 AM, David G. Johnston 
 david.g.johns...@gmail.com wrote:

 On Wednesday, July 15, 2015, Robert DiFalco robert.difa...@gmail.com
 wrote:

 First off I apologize if this is question has been beaten to death. I've
 looked around for a simple answer and could not find one.

 Given a database that will not have it's PKEY or indices modified, is it
 generally faster to INSERT or UPDATE data. And if there is a performance
 difference is it substantial?


 This seems odd.  If you have an option to update but choose to insert
 what becomes of the other record?



 Consider the two pseudo-schemas, I'm just making this up for example
 purposes:

 SCHEMA A
 =
 meal(id SEQUENCE,user_id, started DEFAULT NOW())
 meal_prepared(ref_meal_id, prepared DEFAULT NOW())
 meal_abandoned(ref_meal_id, abandoned ...)
 meal_consumed(ref_meal_id, consumed ...)
 etc.

 Then in response to different meal events you always have an insert.

 aMealId = INSERT INTO meal(user_id) VALUES (aUserId);

 When preparation starts:

 INSERT INTO meal_prepared(ref_meal_id) VALUES (aMealId);

 And so on for each event.

 Compare that to this:

 SCHEMA B
 =
 meal_event(id, started, prepared, abandoned, consumed, ...)

 The start of the meal is an INSERT:

 aMealId = INSERT INTO meal_event(user_id, started) VALUES (aUserId, NOW());

 When preparation starts:

 UPDATE meal_event SET prepared = NOW() WHERE id = aMealId;

 And so on.

 Basically the same data, in one case you always do inserts and add new
 tables for new events. In the other case you only insert once and then
 update for each state, then you add columns if you have new states.

 As I said this is just an example. But in SCHEMA A you have only inserts,
 lots of tables and in SCHEMA B you have a lot of updates and a lot of
 possibly NULL columns if certain events don't occur.

 Is that more clear?


Yes, you are trying to choose between a bunch of one-to-one (optional)
relationships versus adding additional columns to a table all of which can
be null.

I'd argue that neither option is normal (in the DB normalization sense).

CREATE TABLE meal (meal_id bigserial)
CREATE TABLE meal_event_type (meal_event_id bigserial)
CREATE TABLE meal_event (meal_id bigint, meal_event_id bigint, occurred_at
timestamptz)

So now the decision is one of how to denormalize.  materialzed views and
two ways to do so.  The specific solution would depend in part on the final
application queries that you need to write.

If you do want to model the de-normalized form, which I would likely be
tempted to do given a fixed set of events that do not require additional
related attributes, would be to place the few event timestamps on the main
table and UPDATE them to non-null.

In the normal form you will likely find partial indexes to be quite useful.

David J.

Re: [PERFORM] Insert vs Update

On Wed, Jul 15, 2015 at 4:53 PM, Michael Nolan htf...@gmail.com wrote:

 On Wed, Jul 15, 2015 at 3:16 PM, Robert DiFalco robert.difa...@gmail.com
 wrote:


 Thanks David, my example was a big simplification, but I appreciate your
 guidance. The different event types have differing amounts of related data.
 Query speed on this schema is not important, it's really the write speed
 that matters. So I was just wondering given the INSERT or UPDATE approach
 (with no indexed data being changed) if one is likely to be substantially
 faster than the other.


 As I understand how ACID compliance is done, updating a record will
 require updating any indexes for that record, even if the index keys are
 not changing.  That's because any pending transactions still need to be
 able to find the 'old' data, while new transactions need to be able to find
 the 'new' data.  And ACID also means an update is essentially a
 delete-and-insert.


I might be a bit pedantic here but what you describe is a byproduct of the
specific implementation that PostgreSQL uses to affect Consistency (the C
in ACID) as opposed to a forgone outcome in being ACID compliant.

http://www.postgresql.org/docs/9.4/static/mvcc-intro.html

I'm out of my comfort zone here but the HOT optimization is designed to
leverage the fact that an update to a row that does not affect indexed
values is able to leave the index alone and instead during index lookup the
index points to the old tuple, notices that there is a chain present, and
walks that chain to find the currently active tuple.

In short, if the only index is a PK an update of the row can avoid touching
that index.

I mentioned that going from NULL to Not NULL may disrupt this but I'm
thinking I may have mis-spoken.

Also, with separate tables the amount of data to write is going to be less
because you'd have fewer columns on the affected tables.

While an update is a delete+insert a delete is mostly just a bit-flip
action - at least mid-transaction.  Depending on volume, though, the
periodic impact of vaccuming may want to be taken into consideration.

David J.

Re: [PERFORM] Insert vs Update

2015-07-15 Thread Michael Nolan

On Wed, Jul 15, 2015 at 3:16 PM, Robert DiFalco robert.difa...@gmail.com
wrote:


 Thanks David, my example was a big simplification, but I appreciate your
 guidance. The different event types have differing amounts of related data.
 Query speed on this schema is not important, it's really the write speed
 that matters. So I was just wondering given the INSERT or UPDATE approach
 (with no indexed data being changed) if one is likely to be substantially
 faster than the other.


As I understand how ACID compliance is done, updating a record will require
updating any indexes for that record, even if the index keys are not
changing.  That's because any pending transactions still need to be able to
find the 'old' data, while new transactions need to be able to find the
'new' data.  And ACID also means an update is essentially a
delete-and-insert.
--
Mike Nolan

[PERFORM] Insert vs Update

First off I apologize if this is question has been beaten to death. I've
looked around for a simple answer and could not find one.

Given a database that will not have it's PKEY or indices modified, is it
generally faster to INSERT or UPDATE data. And if there is a performance
difference is it substantial?

I have a situation where I can easily do one or the other to the same
effect. For example, I have a journaling schema with a limited number of
states for an entry. Currently each state is it's own table so I just
insert them as they occur. But I could easily have a single entry table
where the row is updated with column information for states (after the
entry's initial insertion).

Not a big deal but since it's so easy for me to take either approach I was
wondering if one was more efficient (for a large DB) than another.

Thanks!

Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-18 Thread Horst Dehmer

Hey Jeff (and others)!

First of all: Thanks for your detailed explanations and guide lines.


On 17.01.2013, at 18:12, Jeff Janes jeff.ja...@gmail.com wrote:

 So the theory is that the presence of idx_4 is causing the trigger to
 pick a poor plan (i.e. one using idx_4) while its absence removes that
 temptation?

Yes. And auto_explain confirms this for the first record 
(obj_item_loc_obj_item_id_idx = idx_4 from last my last mail):

2013-01-18 22:50:21 CET LOG:  duration: 0.021 ms  plan:
Query Text: SELECT * FROM obj_item_loc WHERE obj_item_loc.obj_item_id = 
NEW.obj_item_id AND obj_item_loc.loc_id = NEW.loc_id AND 
obj_item_loc.obj_item_loc_ix = NEW.obj_item_loc_ix
Index Scan using obj_item_loc_obj_item_id_idx on obj_item_loc  
(cost=0.00..8.27 rows=1 width=382)
  Index Cond: (obj_item_id = $15)
  Filter: ((loc_id = $16) AND (obj_item_loc_ix = $17))
2013-01-18 22:50:21 CET CONTEXT:  SQL statement SELECT * FROM obj_item_loc 
WHERE obj_item_loc.obj_item_id = NEW.obj_item_id AND obj_item_loc.loc_id = 
NEW.loc_id AND obj_item_loc.obj_item_loc_ix = NEW.obj_item_loc_ix
PL/pgSQL function obj_item_loc_before_insert() line 5 at SQL statement
COPY obj_item_loc, line 1: 1090301122417276
109030102241000151101090002410029720\N  \N  \N  \N  
\N  \N  \N  \N  \N  \N  \N  \N  \...

and for one of the last records:

2013-01-18 22:53:20 CET LOG:  duration: 16.088 ms  plan:
Query Text: SELECT * FROM obj_item_loc WHERE obj_item_loc.obj_item_id = 
NEW.obj_item_id AND obj_item_loc.loc_id = NEW.loc_id AND 
obj_item_loc.obj_item_loc_ix = NEW.obj_item_loc_ix
Index Scan using obj_item_loc_obj_item_id_idx on obj_item_loc  
(cost=0.00..8.27 rows=1 width=382)
  Index Cond: (obj_item_id = $15)
  Filter: ((loc_id = $16) AND (obj_item_loc_ix = $17))

I see a linear increase of the duration from 0.0x ms to over 16 ms (apart from 
a few nasty outliers with about 22 ms). Although even at the end there are 
still a few durations  0.03 but mostly 15 ms and above.

 True disk reads are much more expensive, but given how few reads you
 have relative to hits, I now think that in aggregate the hits are more
 of a concern than the reads are.  In other words, you seem to be CPU
 bound, not IO bound.

Yes, definitely CPU bound, as top shows 99+% CPU utilization.

 Even more so I think (but not with much confidence) that most of your
 reads are actually coming from the OS cache and not from the disk.
 PG cannot distinguish true disk reads from OS cache reads.
 
 When was the last time you reset the stats?  That is, are your
 reported numbers accumulated over several loads, with some having idx4
 and some not?

I set up a fresh database before each test run. So the stats should be clean.

 More formally, use use auto_explain and set
 auto_explain.log_nested_statements to true.  I haven't verified this
 works with triggers, just going by the description I think it should.

Nice tip! Works for triggers as well.

 Your use case is a little unusual.  If you are bulk loading into an
 initially empty table, usually you would remove the trigger and add it
 after the load (with some kind of bulk operation to make up for
 whatever it was the trigger would have been doing).  On the other
 hand, if you are bulk loading into a live table and so can't drop
 the trigger, then the live table should have good-enough preexisting
 statistics to make the trigger choose a good plan.

My case is indeed unusual as for the whole model of 276 tables there will never 
be an update nor a delete on any row.
The model is rather short-lived, from a few hours to a few months. COPY FROM/TO 
are the only ways to get data into the database and back out. And in between 
there is lots of graph traversal and calculation of convex hulls. But the 
lengthy transaction are by far not the common case.

Having said that, I'm no longer sure if a RDBMS is the right tool for the 
backend. Maybe indexing and storing with a plain full text search engine is. 
Dunno...

Thanks again!

--
Horst

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-17 Thread Jeff Janes

On Tue, Jan 15, 2013 at 3:44 PM, Horst Dehmer horst.deh...@gmail.com wrote:


 idx_4 together with a simple select in the tables on-insert trigger is
 slowing things down considerably.

So the theory is that the presence of idx_4 is causing the trigger to
pick a poor plan (i.e. one using idx_4) while its absence removes that
temptation?


 pg_statio comes up with same big numbers (reads = bad, hits = not so bad?):

True disk reads are much more expensive, but given how few reads you
have relative to hits, I now think that in aggregate the hits are more
of a concern than the reads are.  In other words, you seem to be CPU
bound, not IO bound.

Even more so I think (but not with much confidence) that most of your
reads are actually coming from the OS cache and not from the disk.
PG cannot distinguish true disk reads from OS cache reads.

When was the last time you reset the stats?  That is, are your
reported numbers accumulated over several loads, with some having idx4
and some not?

...

 Now I have some (more) questions:

 1. How do I know which index (if any) is chosen for a select statement
 inside a trigger during a bulk load transaction? (or for that matter: a
 series of recursive plpgsql functions)

Informally, reset your database to the state it was in before the
load, analyze it, and do the explain again before you do the load.

More formally, use use auto_explain and set
auto_explain.log_nested_statements to true.  I haven't verified this
works with triggers, just going by the description I think it should.

 2. The query planner depends on stats collected by auto-vacuum/vacuum
 analyze, right? Does stats collecting also happen during a lengthy
 transaction?

My understanding is that a transaction will not dump its stats until
the commit, so the auto analyze will not occur *due to* the lengthy
transaction until after it is over.  But if the table was already due
for analyze anyway due to previous or concurrent shorter transactions,
the analyze will happen.  However, the lengthy transaction might not
see the results of the analyze (I'm not clear on the transaction
snapshot semantics of the statistics tables) and even if it did see
them, it might just be using cached plans and so would not change the
plan in the middle.

 3. Is it possible (or even advisable) to trigger vacuum analyze inside an
 ongoing transaction. Let's say load 10,000 rows of table A, analyze table A,
 insert the next 10,000 rows, analyze again, ...

You can't vacuum inside a transaction.  You can analyze, but I don't
know if it would be advisable.

Your use case is a little unusual.  If you are bulk loading into an
initially empty table, usually you would remove the trigger and add it
after the load (with some kind of bulk operation to make up for
whatever it was the trigger would have been doing).  On the other
hand, if you are bulk loading into a live table and so can't drop
the trigger, then the live table should have good-enough preexisting
statistics to make the trigger choose a good plan.

Cheers,

Jeff


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-15 Thread Horst Dehmer

After more testing I have gained some insights:

The foreign key constraints are NOT responsible for the low COPY FROM 
performance in my case. I forgot about the indexes which are created along with 
the FK constraints.

Besides the primary key

CONSTRAINT obj_item_loc_pkey PRIMARY KEY (obj_item_id, loc_id, obj_item_loc_ix),

the table OBJ_ITEM_LOC has four additional indexes (let's call them idx_1 
through idx_4)

CREATE INDEX idx_1 ON obj_item_loc USING btree (rec_id);
CREATE INDEX idx_2 ON obj_item_loc USING btree (loc_id);
CREATE INDEX idx_3 ON obj_item_loc USING btree (rptd_id);
CREATE INDEX idx_4 ON obj_item_loc USING btree (obj_item_id);

The indexes 2 to 4 are intended to speed up joins between OBJ_ITEM_LOC and
LOC (loc_id), RPTD (rptd_id) and OBJ_ITEM (obj_item) respectively (and I'm 
highly suspicious if this makes sense at all.)

idx_4 together with a simple select in the tables on-insert trigger is slowing 
things down considerably.
With idx_4 and the trigger rates are

 44100 rows, 0:00:04.576,   9637 r/s: LOC
  2101 rows, 0:00:00.221,   9506 r/s: OBJ_ITEM
  2101 rows, 0:00:00.278,   7557 r/s: ORG
 94713 rows, 0:00:18.502,   5119 r/s: RPTD
 44100 rows, 0:03:03.437,240 r/s: OBJ_ITEM_LOC
imported 187115 record in 0:03:27.081 = 903 r/s

pg_statio comes up with same big numbers (reads = bad, hits = not so bad?):

   relname| heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit 
--++---+---+--
 obj_item_loc |   1262 |   9908013 |  1199 |  1682005
 rptd |   4434 |279022 |  1806 |  1270746
 org  | 38 |191559 |19 |   201071
 obj_item | 84 | 92476 |29 |   104134
 loc  |768 | 88902 |   597 |   352680
(5 rows)

Dropping idx_1, idx_2 and idx_3 at the same time has no significant impact. But 
take away idx_4 only:

 44100 rows, 0:00:04.558,   9675 r/s: LOC
  2101 rows, 0:00:00.220,   9593 r/s: OBJ_ITEM
  2101 rows, 0:00:00.275,   7640 r/s: ORG
 94713 rows, 0:00:18.407,   5145 r/s: RPTD
 44100 rows, 0:00:11.433,   3857 r/s: OBJ_ITEM_LOC
imported 187115 record in 0:00:34.938 = 5355 r/s

Hm, not bad. Now for the select statement in the on insert trigger:

SELECT  * 
FROMobj_item_loc 
WHERE   obj_item_loc.obj_item_id = NEW.obj_item_id 
AND obj_item_loc.loc_id = NEW.loc_id 
AND obj_item_loc.obj_item_loc_ix = NEW.obj_item_loc_ix 
INTOold;

Executing this query AFTER the bulk insert (and probably some auto-vacuuming) 
the query plan looks like this

explain analyze 
select  * 
fromobj_item_loc 
where   (obj_item_id, loc_id, obj_item_loc_ix) =
(10903011224100014650,10903010224100089226,1090002410140894)

QUERY PLAN   
--
 Index Scan using obj_item_loc_loc_id_idx on obj_item_loc  
(cost=0.00..8.36 rows=1 width=329) 
(actual time=0.039..0.040 rows=1 loops=1)
   Index Cond: (loc_id = 10903010224100089226::numeric)
   Filter: ((obj_item_id = 10903011224100014650::numeric) AND 
(obj_item_loc_ix = 1090002410140894::numeric))
 Total runtime: 0.079 ms

After some head-scratching I realized that obj_item_id is just referencing a 
meager 2101 rows which probably makes not for a good index candidate. So, the 
query plan make some sense, I guess.

Now I have some (more) questions:

1. How do I know which index (if any) is chosen for a select statement inside a 
trigger during a bulk load transaction? (or for that matter: a series of 
recursive plpgsql functions)
2. The query planner depends on stats collected by auto-vacuum/vacuum analyze, 
right? Does stats collecting also happen during a lengthy transaction? 
3. Is it possible (or even advisable) to trigger vacuum analyze inside an 
ongoing transaction. Let's say load 10,000 rows of table A, analyze table A, 
insert the next 10,000 rows, analyze again, ...

I'm sorry if this is basic stuff I'm asking here, but especially point 2 is 
bothering me.

--
Kind regards
Horst Dehmer

On 12.01.2013, at 01:17, Jeff Janes jeff.ja...@gmail.com wrote:

 On Friday, January 11, 2013, Horst Dehmer wrote:
 
 Except - and that's the wall I'm hitting - for one table which yielded just 
 75 records/second.
 The main 'problem' seem to be the FK constraints. Dropping just them restored 
 insert performance for this table to 6k records/s.
 
 It sure sounds like you don't have enough RAM to hold the foreign-key table 
 data needed to check the constraints, so every insert needs one disk 
 revolution to fetch the data.
 
 If you drop the indexes and constraints one at a time until it speeds up, is 
 there a certain one that is the culprit? 
 
 You can look in pg_statio_user_tables to see what tables and indexes have

Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-12 Thread Horst Dehmer

Yes, the ids is something I don't like either.
They carry additional semantics, which I cannot make go away.
How are chances char(20) is more time efficient than numeric(20)?
Disk space is no problem here.


On 12.01.2013, at 02:17, Claudio Freire klaussfre...@gmail.com wrote:

 On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer horst.deh...@gmail.com wrote:
 Except - and that's the wall I'm hitting - for one table which yielded just
 75 records/second.
 The main 'problem' seem to be the FK constraints. Dropping just them
 restored insert performance for this table to 6k records/s. The table in
 question has a composite PK (3 columns), 3 foreign keys and a bunch of
 indexes (see table obj_item_loc at the end of the mail). Compared to the
 other 32 tables nothing unusual.
 I'd gladly supply more information if necessary.
 ...
 CREATE TABLE obj_item_loc
 (
  obj_item_id numeric(20,0) NOT NULL,
  loc_id numeric(20,0) NOT NULL,
  obj_item_loc_ix numeric(20,0) NOT NULL,
 
 That sounds a lot like a missing index on the target relations (or
 indices that are unusable).
 
 Those numeric ids look really unusual. Why not bigint? It's close to
 the same precision, but native, faster, more compact, and quite
 unambiguous when indices are involved. If the types don't match on
 both tables, it's quite likely indices won't be used when checking the
 FK, and that spells trouble.



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-12 Thread Jeff Janes

On Fri, Jan 11, 2013 at 5:17 PM, Claudio Freire klaussfre...@gmail.com wrote:
 On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer horst.deh...@gmail.com wrote:
 Except - and that's the wall I'm hitting - for one table which yielded just
 75 records/second.
 The main 'problem' seem to be the FK constraints. Dropping just them
 restored insert performance for this table to 6k records/s. The table in
 question has a composite PK (3 columns), 3 foreign keys and a bunch of
 indexes (see table obj_item_loc at the end of the mail). Compared to the
 other 32 tables nothing unusual.
 I'd gladly supply more information if necessary.
 ...
 CREATE TABLE obj_item_loc
 (
   obj_item_id numeric(20,0) NOT NULL,
   loc_id numeric(20,0) NOT NULL,
   obj_item_loc_ix numeric(20,0) NOT NULL,

 That sounds a lot like a missing index on the target relations (or
 indices that are unusable).

 Those numeric ids look really unusual. Why not bigint? It's close to
 the same precision, but native, faster, more compact, and quite
 unambiguous when indices are involved. If the types don't match on
 both tables, it's quite likely indices won't be used when checking the
 FK, and that spells trouble.

Will PG allow you to add a FK constraint where there is no usable
index on the referenced side?

I have failed to do so, but perhaps I am not being devious enough.

Cheers,

Jeff


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-12 Thread Claudio Freire

On Sat, Jan 12, 2013 at 5:16 PM, Horst Dehmer horst.deh...@gmail.com wrote:
 Yes, the ids is something I don't like either.
 They carry additional semantics, which I cannot make go away.
 How are chances char(20) is more time efficient than numeric(20)?
 Disk space is no problem here.

What are the other tables like then?

The exact data types involved are at issue here, so it matters.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-12 Thread Tom Lane

Jeff Janes jeff.ja...@gmail.com writes:
 Will PG allow you to add a FK constraint where there is no usable
 index on the referenced side?

It will not, because the referenced side must have a unique constraint,
ie an index.

The standard performance gotcha here is not having an index on the
referencing side.  But that only hurts when doing UPDATEs/DELETEs of
referenced-side keys, which as far as I gathered was not the OP's
scenario.

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-12 Thread Horst Dehmer

The types referenced by the foreign keys are the same Numeric(20). 
Since the complete schema (of about 300 tables) is generated, I will just try 
char(20) instead of numeric(20) in the next days to see if it makes any 
difference. Which I somehow doubt. 

But first I'm following the lead of the tables/indexes iostats given by Jeff.


obj_item_loc references the following three tables and there should be no 
surprises.

CREATE UNLOGGED TABLE loc
(
  loc_id numeric(20,0) NOT NULL, 
...
  CONSTRAINT loc_pkey PRIMARY KEY (loc_id),
…
)

CREATE UNLOGGED TABLE obj_item
(
  obj_item_id numeric(20,0) NOT NULL, 
...
  CONSTRAINT obj_item_pkey PRIMARY KEY (obj_item_id),
…
)

CREATE UNLOGGED TABLE rptd
(
  rptd_id numeric(20,0) NOT NULL, 
...
  CONSTRAINT rptd_pkey PRIMARY KEY (rptd_id),
…
)


On 12.01.2013, at 23:18, Claudio Freire klaussfre...@gmail.com wrote:

 On Sat, Jan 12, 2013 at 5:16 PM, Horst Dehmer horst.deh...@gmail.com wrote:
 Yes, the ids is something I don't like either.
 They carry additional semantics, which I cannot make go away.
 How are chances char(20) is more time efficient than numeric(20)?
 Disk space is no problem here.
 
 What are the other tables like then?
 
 The exact data types involved are at issue here, so it matters.



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-12 Thread Claudio Freire

On Sat, Jan 12, 2013 at 7:41 PM, Horst Dehmer horst.deh...@gmail.com wrote:
 Since the complete schema (of about 300 tables) is generated, I will just try 
 char(20) instead of numeric(20) in the next days to see if it makes any 
 difference. Which I somehow doubt.

I think that might just make it worse.

Well, maybe the others were right, and it's just that you're hitting
the disk on that particular table.

That, or it's all those CHECK constraints. Have you tried removing the
CHECK constraints (they're a heapload of function calls)


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-11 Thread Horst Dehmer

Hi!

I see a massive performance drop when writing a large transaction. I'm writing 
data for 33 tables with COPY FROM directly from streams in Scala/Java. Over all 
tables there are 2.2M records which are unevenly distributed from 1 record to 
315k records in some tables.

For comparison I ran a test where I used UNLOGGED tables, no PK/FK constraints, 
nor other constraints or indexes and no triggers for all tables. The insert 
rate for this scenario is well above 105k records/second over all tables (which 
I think is really cool!)

Turning everything on (but still with UNLOGGED tables), i.e. PK/FK, additional 
indexes, some column check constraints and a trigger for each table which 
basically insert one additional record to another table, the rates dropped 
expectedly to around 6k to 7k records/second.

Except - and that's the wall I'm hitting - for one table which yielded just 75 
records/second.
The main 'problem' seem to be the FK constraints. Dropping just them restored 
insert performance for this table to 6k records/s. The table in question has a 
composite PK (3 columns), 3 foreign keys and a bunch of indexes (see table 
obj_item_loc at the end of the mail). Compared to the other 32 tables nothing 
unusual.
I'd gladly supply more information if necessary.

Dropping and recreating constraints/indexes is (for now) no viable alternative, 
since I have to write such transaction into an already populated database.
What I'm trying to understand is, which limit it is I'm hitting here. I need 
some advice how to 'profile' this situation.

Configuration is more or less standard, except WAL settings (which should not 
be relevant here).
EnterpriseDB One Click installer.

Any hint is really appreciated. 
Thanks!

--
Horst Dehmer



PostgreSQL 9.2.1 on x86_64-apple-darwin, compiled by 
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) 
(LLVM build 2336.9.00), 64-bit
OS X 10.8.2
Mid-2012 MacBook Pro 16 GB, 512 GB SSD

bytea_output;escape
checkpoint_completion_target;0.9
checkpoint_segments;32
client_encoding;UNICODE
client_min_messages;notice
lc_collate;en_US.UTF-8
lc_ctype;en_US.UTF-8
listen_addresses;*
log_checkpoints;on
log_destination;stderr
log_line_prefix;%t 
logging_collector;on
max_connections;100
max_stack_depth;2MB
port;5432
server_encoding;UTF8
shared_buffers;24MB
TimeZone;Europe/Vienna
wal_buffers;768kB

/etc/sysctl.conf
kern.sysv.shmmax=1610612736
kern.sysv.shmall=393216
kern.sysv.shmmin=1
kern.sysv.shmmni=256
kern.sysv.shmseg=64
kern.maxprocperuid=512
kern.maxproc=2048

CREATE TABLE obj_item_loc
(
  obj_item_id numeric(20,0) NOT NULL,
  loc_id numeric(20,0) NOT NULL,
  obj_item_loc_ix numeric(20,0) NOT NULL, 
  ver_acc_dim numeric(12,3), 
  horz_acc_dim numeric(12,3), 
  brng_angle numeric(7,4), 
  brng_acc_angle numeric(7,4), 
  brng_precision_code character varying(6), 
  incl_angle numeric(7,4), 
  incl_acc_angle numeric(7,4), 
  incl_precision_code character varying(6), 
  speed_rate numeric(8,4), 
  speed_acc_rate numeric(8,4), 
  speed_precision_code character varying(6), 
  meaning_code character varying(6), 
  rel_speed_code character varying(6), 
  rptd_id numeric(20,0) NOT NULL,
  creator_id numeric(20,0) NOT NULL, 
  update_seqnr numeric(15,0) NOT NULL, 
  rec_id bigint DEFAULT nextval('rec_seq'::regclass),
  CONSTRAINT obj_item_loc_pkey PRIMARY KEY (obj_item_id, loc_id, 
obj_item_loc_ix),
  CONSTRAINT obj_item_loc_4fbc75641175ef1757ca310dd34e34ee_fkey FOREIGN KEY 
(obj_item_id)
  REFERENCES obj_item (obj_item_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT obj_item_loc_7895d64f5557b1e382c36d41212a3696_fkey FOREIGN KEY 
(rptd_id)
  REFERENCES rptd (rptd_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT obj_item_loc_8d919243f69bcc599873caca07ac9888_fkey FOREIGN KEY 
(loc_id)
  REFERENCES loc (loc_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT obj_item_loc_brng_acc_angle_ra_check CHECK 
(br_check_305(brng_acc_angle = 0::numeric AND brng_acc_angle = 359., 
'obj_item_loc'::text, 'brng_acc_angle'::text, brng_acc_angle::text)),
  CONSTRAINT obj_item_loc_brng_angle_ra_check CHECK (br_check_305(brng_angle = 
0::numeric AND brng_angle = 359., 'obj_item_loc'::text, 
'brng_angle'::text, brng_angle::text)),
  CONSTRAINT obj_item_loc_brng_precision_code_check CHECK 
(br_check_305(brng_precision_code::text = ANY (ARRAY['1000MN'::text, 
'100MN'::text, '100SEC'::text, '10DEG'::text, '10MN'::text, '10SEC'::text, 
'DEGREE'::text, 'MIL'::text, 'MINUTE'::text, 'SECOND'::text]), 
'obj_item_loc'::text, 'brng_precision_code'::text, brng_precision_code::text)),
  CONSTRAINT obj_item_loc_incl_acc_angle_ra_check CHECK 
(br_check_305(incl_acc_angle = 0::numeric AND incl_acc_angle = 359., 
'obj_item_loc'::text, 'incl_acc_angle'::text, incl_acc_angle::text)),
  CONSTRAINT obj_item_loc_incl_angle_ra_check CHECK (br_check_305(incl_angle = 
0::numeric AND incl_angle = 359.,

Re: [PERFORM] Insert performance for large transaction with multiple COPY FROM

2013-01-11 Thread Claudio Freire

On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer horst.deh...@gmail.com wrote:
 Except - and that's the wall I'm hitting - for one table which yielded just
 75 records/second.
 The main 'problem' seem to be the FK constraints. Dropping just them
 restored insert performance for this table to 6k records/s. The table in
 question has a composite PK (3 columns), 3 foreign keys and a bunch of
 indexes (see table obj_item_loc at the end of the mail). Compared to the
 other 32 tables nothing unusual.
 I'd gladly supply more information if necessary.
...
 CREATE TABLE obj_item_loc
 (
   obj_item_id numeric(20,0) NOT NULL,
   loc_id numeric(20,0) NOT NULL,
   obj_item_loc_ix numeric(20,0) NOT NULL,

That sounds a lot like a missing index on the target relations (or
indices that are unusable).

Those numeric ids look really unusual. Why not bigint? It's close to
the same precision, but native, faster, more compact, and quite
unambiguous when indices are involved. If the types don't match on
both tables, it's quite likely indices won't be used when checking the
FK, and that spells trouble.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] insert

2011-08-01 Thread Vitalii Tymchyshyn


Hello.

Please note that in multitasking environment you may have problems with 
your code. Two connections may check if a is available and if not (and 
both got empty select result), try to insert. One will succeed, 
another will fail if you have a unique constraint on category name (and 
you'd better have one).


Please note that select for update won't help you much, since this is 
new record you are looking for, and select don't return (and lock) it. I 
am using lock table tableName in SHARE ROW EXCLUSIVE mode in this case.


But then, if you have multiple lookup dictinaries, you need to ensure 
strict order of locking or you will be getting deadlocks. As for me, I 
did create a special application-side class to retrieve such values. If 
I can't find a value in main connection with simple select, I open new 
connection, perform table lock, check if value is in there. If it is 
not, add the value and commit. This may produce orphaned dictionary 
entries (if dictionary entry is committed an main transaction is rolled 
back), but this is usually OK for dictionaries. At the same time I don't 
introduce hard locks into main transaction and don't have to worry about 
deadlocks.


Best regards, Vitalii Tymchyshyn


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] insert

2011-08-01 Thread Kevin Grittner

Vitalii Tymchyshyn tiv...@gmail.com wrote:

Please note that in multitasking environment you may have problems
with your code. Two connections may check if a is available and
if not (and both got empty select result), try to insert. One
will succeed, another will fail if you have a unique constraint on
category name (and you'd better have one).

Please note that select for update won't help you much, since this
is new record you are looking for, and select don't return (and
lock) it. I am using lock table tableName in SHARE ROW
EXCLUSIVE mode in this case.

But then, if you have multiple lookup dictinaries, you need to
ensure strict order of locking or you will be getting deadlocks.
As for me, I did create a special application-side class to
retrieve such values. If I can't find a value in main connection
with simple select, I open new connection, perform table lock,
check if value is in there. If it is not, add the value and
commit. This may produce orphaned dictionary entries (if
dictionary entry is committed an main transaction is rolled back),
but this is usually OK for dictionaries. At the same time I don't
introduce hard locks into main transaction and don't have to worry
about deadlocks.

It sounds like you might want to check out the new truly
serializable transactions in version 9.1. If you can download the
latest beta version of it and test with
default_transaction_isolation = 'serializable' I would be interested
to hear your results. Note that you can't have deadlocks, but you
can have other types of serialization failures, so your software
needs to be prepared to start a transaction over from the beginning
when the SQLSTATE of a failure is '40001'.

The Wiki page which was used to document and organize the work is:

http://wiki.postgresql.org/wiki/Serializable

This is in a little bit of a funny state because not all of the
wording that was appropriate while the feature was under development
(e.g., future tense verbs) has been changed to something more
appropriate for a finished feature, but it should cover the
theoretical ground pretty well. An overlapping document which was
initially based on parts of the Wiki page and has received more
recent attention is the README-SSI file here:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/backend/storage/lmgr/README-SSI;hb=master

Some examples geared toward programmers and DBAs is at this Wiki
page:

http://wiki.postgresql.org/wiki/SSI

It could use a couple more examples and a bit of language cleanup,
but what is there is fairly sound. The largest omission is that we
need to show more explicitly that serialization failures can occur
at times other than COMMIT. (I got a little carried away trying to
show that there was no blocking and that the first committer
wins.)

-Kevin

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] insert

2011-07-31 Thread Gavin Flower


On 30/07/11 08:14, Kevin Grittner wrote:

alanalan.mill...@gmail.com  wrote:


Can I write a BEFORE ROW trigger for the products table  to runs
on INSERT or UPDATE to
  1. insert a new category  return the new category_id  OR
  2.  return the existing category_id for the (to be inserted row)


What would you be using to match an existing category?  If this
accurately identifies a category, why not use it for the key to the
category table, rather than generating a synthetic key value?

-Kevin


Hi Alan,

This is the way I would define the tables, I think it conforms tom your 
requirements, and the definitions look clearer.


I have the convention that the id of the table itself is not prefixed 
with the table name, but references to the id field of other tables are 
(e.g. category_id). This is not something you need to follow, but it 
helps to clearly identify what is a foreign key, and what is the current 
table's id!  Likewise, I think it is simpler to make the table names 
singular, but this again is a bit arbitrary.


I guess, even if you prefer my conventions, it is more important to 
follow the standards of the existing database!



CREATE TABLE product
(
id  SERIAL PRIMARY KEY,
category_id int REFERENCES category(id),
nameVARCHAR(60) NOT NULL
);

CREATE TABLE category
(
id  SERIAL PRIMARY KEY,
nameVARCHAR(20) UNIQUE NOT NULL
);

Though for the primary key of the category table, it might be better to 
explicitly assign the key, then you have more control of the numbers used.


I would be a bit wary of automatically inserting a new category, when 
the given category is not already there, you could end up with several 
variations of spelling for the same category!  I once saw a system with 
about 20 variations of spelling, and number of spaces between words, for 
the name of the same company!


Possibly your client GUI application could have a drop down list of 
available categories, and provision to enter new ones, but then this 
might be outside your control.



Cheers,
GAvin

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] insert

2011-07-29 Thread alan

I think I figured it out myself.
If anyone sees issues with this (simple) approach, please let me know.

I changed my table definitions to this:

CREATE SEQUENCE public.product_id_seq
CREATE TABLE products (
product_id INTEGER DEFAULT nextval('product_id_seq'::regclass) NOT
NULL,
name VARCHAR(60) NOT NULL,
category SMALLINT  NOT NULL,
CONSTRAINT product_id PRIMARY KEY (product_id)
);
CREATE SEQUENCE public.category_id_seq
CREATE TABLE category (
   category_id INTEGER DEFAULT nextval('category_id_seq'::regclass)
NOT NULL,
   name VARCHAR(20) NOT NULL,
   CONSTRAINT category_id PRIMARY KEY (category_id)
);
ALTER TABLE products ADD CONSTRAINT category_products_fk
FOREIGN KEY (category)
REFERENCES category (category_id)
ON DELETE NO ACTION ON UPDATE CASCADE
;

Then created this function:

CREATE OR REPLACE FUNCTION getid(_table text,_pk text,_name text)
RETURNS integer AS $$
DECLARE _id integer;
BEGIN
  EXECUTE 'SELECT '
|| _pk
|| ' FROM '
|| _table::regclass
|| ' WHERE name'
|| ' = '
|| quote_literal(_name)
   INTO _id;

  IF _id  0 THEN
return _id;
  ELSE
EXECUTE 'INSERT INTO '
 || _table
 || ' VALUES (DEFAULT,' || quote_literal(_name) || ')'
 || ' RETURNING ' || _pk
INTO _id;
return _id;
  END IF;
END;
$$
 LANGUAGE 'plpgsql' VOLATILE;

Now I can just insert into the products table via:

INSERT INTO products VALUES(DEFAULT,'Postgresql for
Dummies',getid('category','category_id','books'));

For example:

testdb=# select * from products;
 product_id | name | category
+--+--
(0 rows)

iims_test=# select * from category;
 category_id | name
-+--
(0 rows)

testdb=# insert into products values(DEFAULT,'Postgresql for
Dummies',getid('category','category_id','books'));
INSERT 0 1

testdb=# select * from
category;
 category_id | name
-+---
   1 | books

testdb=# select * from products;
 product_id |  name  | category
++--
  1 | Postgresql for Dummies |1

Updating the category_id in category table are also cascaded to the
product table.

testdb=# UPDATE category SET category_id = 2 WHERE category_id = 1;
UPDATE 1

testdb=# SELECT * FROM products;
 product_id |  name  | category
++--
  1 | Postgresql for Dummies |2


Alan

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] insert

2011-07-29 Thread alan

next question.

I have a product table with a 'category column that I want to
maintain in a separate table.

CREATE TABLE products (
product_id INTEGER DEFAULT
nextval('product_id_seq'::regclass) NOT NULL,
name VARCHAR(60) NOT NULL,
category SMALLINT  NOT NULL,
CONSTRAINT product_id PRIMARY KEY (product_id)
);
CREATE TABLE products (
category_id INTEGER DEFAULT
nextval('category_id_seq'::regclass) NOT NULL,
name VARCHAR(20) NOT NULL,
CONSTRAINT category_id PRIMARY KEY (category_id)
);

Every product must have a category,
Since many (but not all) products have the same category I only want 1
table with unique categories.

To do the insert into the products table I need to retrieve or insert
the category_id in categories first.
Which means more code on my client app (if ($cat_id =
get_cat_id($cat)) }else { $cat_id = insert_cat($cat)})

Can I write a BEFORE ROW trigger for the products table  to runs on
INSERT or UPDATE to
 1. insert a new category  return the new category_id  OR
 2.  return the existing category_id for the (to be inserted row)

Alan
I donproducts.category to be a foreign key that points to the uniqie
category_id id in the want to keep I need to do get the cate

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] insert

2011-07-29 Thread Kevin Grittner

alan alan.mill...@gmail.com wrote:
 
 Can I write a BEFORE ROW trigger for the products table  to runs
 on INSERT or UPDATE to
  1. insert a new category  return the new category_id  OR
  2.  return the existing category_id for the (to be inserted row)
 
What would you be using to match an existing category?  If this
accurately identifies a category, why not use it for the key to the
category table, rather than generating a synthetic key value?
 
-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] INSERT query times

2011-07-10 Thread sergio mayoral

Hi,

i am using libpq library and postgresql 8.4 for my linux application running
on ARM with 256 MB. I am just doing:

PQconnectdb();
PQexec(INSERT INTO table1 ); (0.009661 sec.)
PQexec(INSERT INTO table1 ); (0.004208 sec.)

PQexec(INSERT INTO table2 ); (0.007352 sec.)
PQexec(INSERT INTO table2 ); (0.002533 sec.)
PQexec(INSERT INTO table2 ); (0.002281 sec.)
PQexec(INSERT INTO table2 ); (0.002244 sec.)

PQexec(INSERT INTO table3 ); (0.006903 sec.)
PQexec(INSERT INTO table3 ); (0.002903 sec.)
PQfinnish();

I check the time for each PQexec with gettimeofday function and I always see
that the first INSERT for each table needs longer than the next ones.

this must be something with the parser stage and since i am doing every time
the same queries, I would like to know if there is a way to cache this
queries in order to speed up the first INSERTs.

Thanks in advance,

Sergio

Re: [PERFORM] INSERT query times

2011-07-10 Thread Pavel Stehule

Hello

a) look on COPY statement and COPY API protocol - it can be 100x
faster than INSERTS
http://www.postgresql.org/docs/8.3/static/libpq-copy.html

b) if you can't to use COPY use:

* outer transaction - BEGIN, INSERT, INSERT ... COMMIT if this is possible
* use a prepared statement
http://www.postgresql.org/docs/8.3/static/sql-prepare.html

if you cannot to use a outer transaction, and you can to replay a
process, if there are some problems, use a asynchronnous commit
http://www.postgresql.org/docs/8.3/static/wal-async-commit.html

Regards

Pavel Stehule


2011/7/7 sergio mayoral smayo...@gmail.com:
 Hi,
 i am using libpq library and postgresql 8.4 for my linux application running
 on ARM with 256 MB. I am just doing:
 PQconnectdb();
 PQexec(INSERT INTO table1 ); (0.009661 sec.)
 PQexec(INSERT INTO table1 ); (0.004208 sec.)
 PQexec(INSERT INTO table2 ); (0.007352 sec.)
 PQexec(INSERT INTO table2 ); (0.002533 sec.)
 PQexec(INSERT INTO table2 ); (0.002281 sec.)
 PQexec(INSERT INTO table2 ); (0.002244 sec.)
 PQexec(INSERT INTO table3 ); (0.006903 sec.)
 PQexec(INSERT INTO table3 ); (0.002903 sec.)
 PQfinnish();
 I check the time for each PQexec with gettimeofday function and I always see
 that the first INSERT for each table needs longer than the next ones.
 this must be something with the parser stage and since i am doing every time
 the same queries, I would like to know if there is a way to cache this
 queries in order to speed up the first INSERTs.
 Thanks in advance,
 Sergio

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] INSERT query times

2011-07-10 Thread Tom Lane

sergio mayoral smayo...@gmail.com writes:
 i am using libpq library and postgresql 8.4 for my linux application running
 on ARM with 256 MB. I am just doing:

 PQconnectdb();
 PQexec(INSERT INTO table1 ); (0.009661 sec.)
 PQexec(INSERT INTO table1 ); (0.004208 sec.)

 PQexec(INSERT INTO table2 ); (0.007352 sec.)
 PQexec(INSERT INTO table2 ); (0.002533 sec.)
 PQexec(INSERT INTO table2 ); (0.002281 sec.)
 PQexec(INSERT INTO table2 ); (0.002244 sec.)

 PQexec(INSERT INTO table3 ); (0.006903 sec.)
 PQexec(INSERT INTO table3 ); (0.002903 sec.)
 PQfinnish();

 I check the time for each PQexec with gettimeofday function and I always see
 that the first INSERT for each table needs longer than the next ones.

The first few commands of *any* type on a new connection are going to
take longer than repeat versions of those commands, because the backend
needs to load up its internal caches.  Once it's cached information
about the tables, operators, etc that you are working with, it's a bit
faster.  This isn't specific to INSERT.

 this must be something with the parser stage and since i am doing every time
 the same queries, I would like to know if there is a way to cache this
 queries in order to speed up the first INSERTs.

The only fix is to hang onto your connections longer.  Consider a
connection pooler.

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

2010-11-02 Thread hubert depesz lubaczewski

On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
    CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
  )
  the index definition is
  CREATE INDEX PK_AT2
    ON ABC
    USING btree
    (event, tableindex)
  TABLESPACE sample;
 
 Indexing twice the same column is useless. (perhaps move your PK to
 the tablespace 'sample' is good too ?)

why do you say that?
these are not the same indexes and they serve different purposes.

Best regards,

depesz

-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

2010-11-02 Thread Cédric Villemain

2010/11/2 hubert depesz lubaczewski dep...@depesz.com:
 On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
    CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
  )
  the index definition is
  CREATE INDEX PK_AT2
    ON ABC
    USING btree
    (event, tableindex)
  TABLESPACE sample;

 Indexing twice the same column is useless. (perhaps move your PK to
 the tablespace 'sample' is good too ?)

 why do you say that?
 these are not the same indexes and they serve different purposes.

Given that tableindex is the PK column, I really like to now the usage
pattern for having it indexed twice.


 Best regards,

 depesz

 --
 Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
 jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007




-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

2010-11-02 Thread hubert depesz lubaczewski

On Tue, Nov 02, 2010 at 12:04:42PM +0100, Cédric Villemain wrote:
 2010/11/2 hubert depesz lubaczewski dep...@depesz.com:
  On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
     CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
   )
   the index definition is
   CREATE INDEX PK_AT2
     ON ABC
     USING btree
     (event, tableindex)
   TABLESPACE sample;
 
  Indexing twice the same column is useless. (perhaps move your PK to
  the tablespace 'sample' is good too ?)
 
  why do you say that?
  these are not the same indexes and they serve different purposes.
 
 Given that tableindex is the PK column, I really like to now the usage
 pattern for having it indexed twice.

select * from table where event = 123 order by tableindex desc limit 50;

Best regards,

depesz

-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

2010-11-02 Thread Divakar Singh

May be a query that is filtering based on these 2 columns?

 Best Regards,
Divakar





From: Cédric Villemain cedric.villemain.deb...@gmail.com
To: dep...@depesz.com
Cc: Divakar Singh dpsma...@yahoo.com; pgsql-performance@postgresql.org
Sent: Tue, November 2, 2010 4:34:42 PM
Subject: Re: [PERFORM] Insert performance with composite index

2010/11/2 hubert depesz lubaczewski dep...@depesz.com:
 On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
  )
  the index definition is
  CREATE INDEX PK_AT2
ON ABC
USING btree
(event, tableindex)
  TABLESPACE sample;

 Indexing twice the same column is useless. (perhaps move your PK to
 the tablespace 'sample' is good too ?)

 why do you say that?
 these are not the same indexes and they serve different purposes.

Given that tableindex is the PK column, I really like to now the usage
pattern for having it indexed twice.


 Best regards,

 depesz

 --
 Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
 jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007




-- 
Cédric Villemain   2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

2010-11-02 Thread Cédric Villemain

2010/11/2 hubert depesz lubaczewski dep...@depesz.com:
 On Tue, Nov 02, 2010 at 12:04:42PM +0100, Cédric Villemain wrote:
 2010/11/2 hubert depesz lubaczewski dep...@depesz.com:
  On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
     CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
   )
   the index definition is
   CREATE INDEX PK_AT2
     ON ABC
     USING btree
     (event, tableindex)
   TABLESPACE sample;
 
  Indexing twice the same column is useless. (perhaps move your PK to
  the tablespace 'sample' is good too ?)
 
  why do you say that?
  these are not the same indexes and they serve different purposes.

 Given that tableindex is the PK column, I really like to now the usage
 pattern for having it indexed twice.

 select * from table where event = 123 order by tableindex desc limit 50;

Correct. Thanks Hubert.

-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Insert performance with composite index

Hi,
I am trying to tune my libpq program for insert performance.
When I tried inserting 1M rows into a table with a Primary Key, it took almost 
62 seconds.
After adding a composite index of 2 columns, the performance degrades to 125 
seconds.
I am using COPY to insert all data in 1 transaction.

the table definition is 

CREATE TABLE ABC
(
  event integer,
  innodeid character varying(80),
  innodename character varying(80),
  sourceid character varying(300),
  intime timestamp(3) without time zone,
  outnodeid character varying(80),
  outnodename character varying(80),
  destinationid character varying(300),
  outtime timestamp(3) without time zone,
  bytes integer,
  cdrs integer,
  tableindex integer NOT NULL,
  noofsubfilesinfile integer,
  recordsequenceintegerlist character varying(1000),
  CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
)

the index definition is 


CREATE INDEX PK_AT2
  ON ABC
  USING btree
  (event, tableindex)
TABLESPACE sample;

Any tip to increase the insert performance in this case?

It would also be helpful if someone can send comprehensive libpq programming 
guide for PG 9.x. Online doc of libpq is not much helpful for a newbie like me.


 Best Regards,
Divakar

Re: [PERFORM] Insert performance with composite index

Hi Marti,
Thanks for your tips. i will try those.
I am on Solaris Sparc 5.10

 Best Regards,
Divakar





From: Marti Raudsepp ma...@juffo.org
To: Divakar Singh dpsma...@yahoo.com
Cc: pgsql-performance@postgresql.org
Sent: Mon, November 1, 2010 6:23:17 PM
Subject: Re: [PERFORM] Insert performance with composite index

On Mon, Nov 1, 2010 at 14:49, Divakar Singh dpsma...@yahoo.com wrote:
 I am trying to tune my libpq program for insert performance.
 When I tried inserting 1M rows into a table with a Primary Key, it took
 almost 62 seconds.
 After adding a composite index of 2 columns, the performance degrades to 125
 seconds.

This sounds a lot like the bottleneck I was hitting. What Linux kernel
version are you running?

If it's 2.6.33 or later, see:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#wal_sync_method_wal_buffers

http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php

Regards,
Marti

Re: [PERFORM] Insert performance with composite index

2010-11-01 Thread Marti Raudsepp

On Mon, Nov 1, 2010 at 14:49, Divakar Singh dpsma...@yahoo.com wrote:
 I am trying to tune my libpq program for insert performance.
 When I tried inserting 1M rows into a table with a Primary Key, it took
 almost 62 seconds.
 After adding a composite index of 2 columns, the performance degrades to 125
 seconds.

This sounds a lot like the bottleneck I was hitting. What Linux kernel
version are you running?

If it's 2.6.33 or later, see:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#wal_sync_method_wal_buffers
http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php

Regards,
Marti

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

2010-11-01 Thread Marti Raudsepp

On Mon, Nov 1, 2010 at 14:56, Divakar Singh dpsma...@yahoo.com wrote:
 Thanks for your tips. i will try those.
 I am on Solaris Sparc 5.10

Sorry, I assumed you were running Linux. But still it could be the
same problem as I had.

Be careful changing your wal_sync_method, as it has the potential to
corrupt your database. I have no experience with Solaris.

For what it's worth, Jignesh Shah recommends using
wal_sync_method=fsync on Solaris:
http://blogs.sun.com/jkshah/entry/postgresql_on_solaris_better_use
http://blogs.sun.com/jkshah/entry/postgresql_wal_sync_method_and

Regards,
Marti

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

2010-11-01 Thread Cédric Villemain

2010/11/1 Divakar Singh dpsma...@yahoo.com:
 Hi,
 I am trying to tune my libpq program for insert performance.
 When I tried inserting 1M rows into a table with a Primary Key, it took
 almost 62 seconds.
 After adding a composite index of 2 columns, the performance degrades to 125
 seconds.
 I am using COPY to insert all data in 1 transaction.

 the table definition is

 CREATE TABLE ABC
 (
   event integer,
   innodeid character varying(80),
   innodename character varying(80),
   sourceid character varying(300),
   intime timestamp(3) without time zone,
   outnodeid character varying(80),
   outnodename character varying(80),
   destinationid character varying(300),
   outtime timestamp(3) without time zone,
   bytes integer,
   cdrs integer,
   tableindex integer NOT NULL,
   noofsubfilesinfile integer,
   recordsequenceintegerlist character varying(1000),
   CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex)
 )

 the index definition is


 CREATE INDEX PK_AT2
   ON ABC
   USING btree
   (event, tableindex)
 TABLESPACE sample;

Indexing twice the same column is useless. (perhaps move your PK to
the tablespace 'sample' is good too ?)


 Any tip to increase the insert performance in this case?

If you create or truncate  table then copy to it, you should create
index after the copy order.


 It would also be helpful if someone can send comprehensive libpq programming
 guide for PG 9.x. Online doc of libpq is not much helpful for a newbie like
 me.


 Best Regards,
 Divakar





-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

Hi,

On Monday 01 November 2010 13:49:14 Divakar Singh wrote:
 When I tried inserting 1M rows into a table with a Primary Key, it took
 almost 62 seconds.
 After adding a composite index of 2 columns, the performance degrades to
 125 seconds.
 I am using COPY to insert all data in 1 transaction.
Without seeing your config its hard to suggest anything here. Did you do basic 
tuning of your pg installation?

wal_buffers, shared_buffers, checkpoint_segments, maintenance_work_mem are 
likely most relevant for that specific case.

Andres

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

On Monday 01 November 2010 15:08:10 Divakar Singh wrote:
 here are my parameters:
Which pg version is that?

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

I am using 9.0.1

 Best Regards,
Divakar





From: Andres Freund and...@anarazel.de
To: Divakar Singh dpsma...@yahoo.com
Cc: pgsql-performance@postgresql.org
Sent: Mon, November 1, 2010 7:44:31 PM
Subject: Re: [PERFORM] Insert performance with composite index

On Monday 01 November 2010 15:08:10 Divakar Singh wrote:
 here are my parameters:
Which pg version is that?

Re: [PERFORM] Insert performance with composite index

On Monday 01 November 2010 15:16:49 Divakar Singh wrote:
 I am using 9.0.1
Either thats not true or you cargo culted loads of your config from a 
significantly older pg version.

Things like:

#bgwriter_delay = 200# 10-1 milliseconds between rounds
bgwriter_lru_percent = 0# 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5# 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333# 0-100% of all buffers scanned/round
bgwriter_all_maxpages = 0# 0-1000 buffers max written/round

make me very suspicious.

As I said, I would check the variables I referenced in my first post...

Andres

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Insert performance with composite index

Do you mean these parameters have been removed starting 9.X?
As I see on 
http://www.network-theory.co.uk/docs/postgresql/vol3/BackgroundWriter.html 
,these parameters were added starting from 8.0 right?


 Best Regards,
Divakar





From: Andres Freund and...@anarazel.de
To: Divakar Singh dpsma...@yahoo.com
Cc: pgsql-performance@postgresql.org
Sent: Mon, November 1, 2010 7:50:59 PM
Subject: Re: [PERFORM] Insert performance with composite index

On Monday 01 November 2010 15:16:49 Divakar Singh wrote:
 I am using 9.0.1
Either thats not true or you cargo culted loads of your config from a 
significantly older pg version.

Things like:

#bgwriter_delay = 200# 10-1 milliseconds between rounds
bgwriter_lru_percent = 0# 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5# 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333# 0-100% of all buffers scanned/round
bgwriter_all_maxpages = 0# 0-1000 buffers max written/round

make me very suspicious.

As I said, I would check the variables I referenced in my first post...

Andres

Re: [PERFORM] Insert performance with composite index