pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbest performance for simple dml
8+ messages / 3 participants
[nested] [flat]
* best performance for simple dml
@ 2011-06-27 06:09 chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: chester c young @ 2011-06-27 06:09 UTC (permalink / raw)
To: pgsql-sql
what is the best performance / best practices for frequently-used simple dml, for example, an insert
1. fast-interface
2. prepared statement calling "insert ..." with binary parameters
3. prepared statement calling "myfunc(..." with binary parameters; myfunc takes its arguments and performs an insert using them
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: best performance for simple dml
@ 2011-06-27 06:35 Pavel Stehule <pavel.stehule@gmail.com>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2011-06-27 06:35 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
Hello
try it and you will see. Depends on network speed, hw speed. But the most
fast is using a COPY API
http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html
Regards
Pavel Stehule
2011/6/27 chester c young <chestercyoung@yahoo.com>
> what is the best performance / best practices for frequently-used simple
> dml, for example, an insert
>
> 1. fast-interface
>
> 2. prepared statement calling "insert ..." with binary parameters
>
> 3. prepared statement calling "myfunc(..." with binary parameters; myfunc
> takes its arguments and performs an insert using them
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: best performance for simple dml
@ 2011-06-27 06:48 chester c young <chestercyoung@yahoo.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: chester c young @ 2011-06-27 06:48 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
two questions:
I thought copy was for multiple rows - is its setup cost effective for one row?
copy would also only be good for insert or select, not update - is this right?
--- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com> wrote:
From: Pavel Stehule <pavel.stehule@gmail.com>
Subject: Re: [SQL] best performance for simple dml
To: "chester c young" <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Date: Monday, June 27, 2011, 12:35 AM
Hello
try it and you will see. Depends on network speed, hw speed. But the most fast is using a COPY API
http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html
Regards
Pavel Stehule
2011/6/27 chester c young <chestercyoung@yahoo.com>
what is the best performance / best practices for frequently-used simple dml, for example, an insert
1. fast-interface
2. prepared statement calling "insert ..." with binary parameters
3. prepared statement calling "myfunc(..." with binary parameters; myfunc takes its arguments and performs an insert using them
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: best performance for simple dml
@ 2011-06-27 07:05 Pavel Stehule <pavel.stehule@gmail.com>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 3 replies; 8+ messages in thread
From: Pavel Stehule @ 2011-06-27 07:05 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
2011/6/27 chester c young <chestercyoung@yahoo.com>
>
> two questions:
> I thought copy was for multiple rows - is its setup cost effective for one row?
I expect it will be faster for one row too - it is not sql statement
if you want to understand to performance issues you have to understand to
a) network communication costs
b) SQL parsing and SQL planning costs
c) commits costs
d) other costs - triggers, referential integrity costs
>
> copy would also only be good for insert or select, not update - is this right?
sure,
If you need to call a lot of simple dml statement in cycle, then
a) try tu move it to stored function
b) if you can't to move it, then ensure, so statements will be
executed under outer transaction
slow code
for(i = 0; i < 1000; i++)
exec("insert into foo values($1), itoa(i));
10x faster code
exec('begin');
for(i = 0; i < 1000; i++)
exec("insert into foo values($1), itoa(i));
exec('commit');
Regards
Pavel Stehule
>
> --- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> From: Pavel Stehule <pavel.stehule@gmail.com>
> Subject: Re: [SQL] best performance for simple dml
> To: "chester c young" <chestercyoung@yahoo.com>
> Cc: pgsql-sql@postgresql.org
> Date: Monday, June 27, 2011, 12:35 AM
>
> Hello
>
> try it and you will see. Depends on network speed, hw speed. But the most fast is using a COPY API
>
> http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html
>
> Regards
>
> Pavel Stehule
>
>
> 2011/6/27 chester c young <chestercyoung@yahoo.com>
>
> what is the best performance / best practices for frequently-used simple dml, for example, an insert
> 1. fast-interface
> 2. prepared statement calling "insert ..." with binary parameters
> 3. prepared statement calling "myfunc(..." with binary parameters; myfunc takes its arguments and performs an insert using them
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: best performance for simple dml
@ 2011-06-27 07:49 chester c young <chestercyoung@yahoo.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: chester c young @ 2011-06-27 07:49 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
very nice pointers. thank you very much!
--- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com> wrote:
From: Pavel Stehule <pavel.stehule@gmail.com>
Subject: Re: [SQL] best performance for simple dml
To: "chester c young" <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Date: Monday, June 27, 2011, 1:05 AM
2011/6/27 chester c young <chestercyoung@yahoo.com>
>
> two questions:
> I thought copy was for multiple rows - is its setup cost effective for one row?
I expect it will be faster for one row too - it is not sql statement
if you want to understand to performance issues you have to understand to
a) network communication costs
b) SQL parsing and SQL planning costs
c) commits costs
d) other costs - triggers, referential integrity costs
>
> copy would also only be good for insert or select, not update - is this right?
sure,
If you need to call a lot of simple dml statement in cycle, then
a) try tu move it to stored function
b) if you can't to move it, then ensure, so statements will be
executed under outer transaction
slow code
for(i = 0; i < 1000; i++)
exec("insert into foo values($1), itoa(i));
10x faster code
exec('begin');
for(i = 0; i < 1000; i++)
exec("insert into foo values($1), itoa(i));
exec('commit');
Regards
Pavel Stehule
>
> --- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> From: Pavel Stehule <pavel.stehule@gmail.com>
> Subject: Re: [SQL] best performance for simple dml
> To: "chester c young" <chestercyoung@yahoo.com>
> Cc: pgsql-sql@postgresql.org
> Date: Monday, June 27, 2011, 12:35 AM
>
> Hello
>
> try it and you will see. Depends on network speed, hw speed. But the most fast is using a COPY API
>
> http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html
>
> Regards
>
> Pavel Stehule
>
>
> 2011/6/27 chester c young <chestercyoung@yahoo.com>
>
> what is the best performance / best practices for frequently-used simple dml, for example, an insert
> 1. fast-interface
> 2. prepared statement calling "insert ..." with binary parameters
> 3. prepared statement calling "myfunc(..." with binary parameters; myfunc takes its arguments and performs an insert using them
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: best performance for simple dml
@ 2011-06-27 08:02 Craig Ringer <craig@postnewspapers.com.au>
parent: Pavel Stehule <pavel.stehule@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Craig Ringer @ 2011-06-27 08:02 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: chester c young <chestercyoung@yahoo.com>; pgsql-sql
On 27/06/11 15:05, Pavel Stehule wrote:
> exec('begin');
> for(i = 0; i < 1000; i++)
> exec("insert into foo values($1), itoa(i));
> exec('commit');
You can probably also benefit from multi-valued INSERTs, though I
haven't verified this.
INSERT INTO foo VALUES
(1,'joe','dean'),
(4,'fred','bob'),
(11,'anne','smith');
There'll be a threshhold above which the COPY protocol becomes faster,
though.
--
Craig Ringer
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: best performance for simple dml
@ 2011-06-27 13:02 chester c young <chestercyoung@yahoo.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: chester c young @ 2011-06-27 13:02 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
forgive me for brain storming a little re copy:
if there are a limited number of tables you're inserting, would there be anything wrong with the app opening a copy connection? ie, a connection initiates the copy and then stays open like a pipe for any inserts coming through it. visually it's a very cool paradigm, but is it actually a good idea?
--- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com> wrote:
From: Pavel Stehule <pavel.stehule@gmail.com>
Subject: Re: [SQL] best performance for simple dml
To: "chester c young" <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Date: Monday, June 27, 2011, 1:05 AM
2011/6/27 chester c young <chestercyoung@yahoo.com>
>
> two questions:
> I thought copy was for multiple rows - is its setup cost effective for one row?
I expect it will be faster for one row too - it is not sql statement
if you want to understand to performance issues you have to understand to
a) network communication costs
b) SQL parsing and SQL planning costs
c) commits costs
d) other costs - triggers, referential integrity costs
>
> copy would also only be good for insert or select, not update - is this right?
sure,
If you need to call a lot of simple dml statement in cycle, then
a) try tu move it to stored function
b) if you can't to move it, then ensure, so statements will be
executed under outer transaction
slow code
for(i = 0; i < 1000; i++)
exec("insert into foo values($1), itoa(i));
10x faster code
exec('begin');
for(i = 0; i < 1000; i++)
exec("insert into foo values($1), itoa(i));
exec('commit');
Regards
Pavel Stehule
>
> --- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> From: Pavel Stehule <pavel.stehule@gmail.com>
> Subject: Re: [SQL] best performance for simple dml
> To: "chester c young" <chestercyoung@yahoo.com>
> Cc: pgsql-sql@postgresql.org
> Date: Monday, June 27, 2011, 12:35 AM
>
> Hello
>
> try it and you will see. Depends on network speed, hw speed. But the most fast is using a COPY API
>
> http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html
>
> Regards
>
> Pavel Stehule
>
>
> 2011/6/27 chester c young <chestercyoung@yahoo.com>
>
> what is the best performance / best practices for frequently-used simple dml, for example, an insert
> 1. fast-interface
> 2. prepared statement calling "insert ..." with binary parameters
> 3. prepared statement calling "myfunc(..." with binary parameters; myfunc takes its arguments and performs an insert using them
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: best performance for simple dml
@ 2011-06-27 13:40 Pavel Stehule <pavel.stehule@gmail.com>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Pavel Stehule @ 2011-06-27 13:40 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
Hello
2011/6/27 chester c young <chestercyoung@yahoo.com>
> forgive me for brain storming a little re copy:
>
> if there are a limited number of tables you're inserting, would there be
> anything wrong with the app opening a copy connection? ie, a connection
> initiates the copy and then stays open like a pipe for any inserts coming
> through it. visually it's a very cool paradigm, but is it actually a good
> idea?
>
depends on application. Usually you can use a connection better than just
"insert connection". I am thinking, so it doesn't carry some special - it
remove a connection cost, but nothing more. You can use a more connections
to do paralel inserts - it has a sense.
look on pgpool or other similar sw for connection pooling
Pavel
>
> --- On *Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com>* wrote:
>
>
> From: Pavel Stehule <pavel.stehule@gmail.com>
> Subject: Re: [SQL] best performance for simple dml
> To: "chester c young" <chestercyoung@yahoo.com>
> Cc: pgsql-sql@postgresql.org
> Date: Monday, June 27, 2011, 1:05 AM
>
> 2011/6/27 chester c young <chestercyoung@yahoo.com<http://mc/compose?to=chestercyoung@yahoo.com;
> >
> >
> > two questions:
> > I thought copy was for multiple rows - is its setup cost effective for
> one row?
>
> I expect it will be faster for one row too - it is not sql statement
>
> if you want to understand to performance issues you have to understand to
>
> a) network communication costs
> b) SQL parsing and SQL planning costs
> c) commits costs
> d) other costs - triggers, referential integrity costs
>
> >
> > copy would also only be good for insert or select, not update - is this
> right?
>
> sure,
>
> If you need to call a lot of simple dml statement in cycle, then
>
> a) try tu move it to stored function
> b) if you can't to move it, then ensure, so statements will be
> executed under outer transaction
>
> slow code
>
> for(i = 0; i < 1000; i++)
> exec("insert into foo values($1), itoa(i));
>
> 10x faster code
>
> exec('begin');
> for(i = 0; i < 1000; i++)
> exec("insert into foo values($1), itoa(i));
> exec('commit');
>
> Regards
>
> Pavel Stehule
>
> >
> > --- On Mon, 6/27/11, Pavel Stehule <pavel.stehule@gmail.com<http://mc/compose?to=pavel.stehule@gmail.com>;
> wrote:
> >
> > From: Pavel Stehule <pavel.stehule@gmail.com<http://mc/compose?to=pavel.stehule@gmail.com;
> >
> > Subject: Re: [SQL] best performance for simple dml
> > To: "chester c young" <chestercyoung@yahoo.com<http://mc/compose?to=chestercyoung@yahoo.com;
> >
> > Cc: pgsql-sql@postgresql.org<http://mc/compose?to=pgsql-sql@postgresql.org;
> > Date: Monday, June 27, 2011, 12:35 AM
> >
> > Hello
> >
> > try it and you will see. Depends on network speed, hw speed. But the most
> fast is using a COPY API
> >
> > http://www.postgresql.org/docs/9.0/interactive/libpq-copy.html
> >
> > Regards
> >
> > Pavel Stehule
> >
> >
> > 2011/6/27 chester c young <chestercyoung@yahoo.com<http://mc/compose?to=chestercyoung@yahoo.com;
> >
> >
> > what is the best performance / best practices for frequently-used simple
> dml, for example, an insert
> > 1. fast-interface
> > 2. prepared statement calling "insert ..." with binary parameters
> > 3. prepared statement calling "myfunc(..." with binary parameters; myfunc
> takes its arguments and performs an insert using them
> >
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org<http://mc/compose?to=pgsql-sql@postgresql.org;
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-06-27 13:40 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-27 06:09 best performance for simple dml chester c young <chestercyoung@yahoo.com>
2011-06-27 06:35 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-06-27 06:48 ` chester c young <chestercyoung@yahoo.com>
2011-06-27 07:05 ` Pavel Stehule <pavel.stehule@gmail.com>
2011-06-27 07:49 ` chester c young <chestercyoung@yahoo.com>
2011-06-27 08:02 ` Craig Ringer <craig@postnewspapers.com.au>
2011-06-27 13:02 ` chester c young <chestercyoung@yahoo.com>
2011-06-27 13:40 ` Pavel Stehule <pavel.stehule@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox