pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
profiling postgresql queries?
6+ messages / 4 participants
[nested] [flat]

* profiling postgresql queries?
@ 2005-04-12 10:46 hubert lubaczewski <hubert.lubaczewski@eo.pl>
  2005-04-12 14:10 ` Re: profiling postgresql queries? Tom Lane <tgl@sss.pgh.pa.us>
  2005-04-12 14:43 ` Re: profiling postgresql queries? Michael Fuhr <mike@fuhr.org>
  0 siblings, 2 replies; 6+ messages in thread

From: hubert lubaczewski @ 2005-04-12 10:46 UTC (permalink / raw)
  To: pgsql-performance

hi
i'm not totally sure i should ask on this mailing list - so if you think
i should better ask someplace else, please let me know.

the problem i have is that specific queries (inserts and updates) take a
long time to run.

of course i do vacuum analyze frequently. i also use explain analyze on
queries.

the problem is that both the inserts and updated operate on
heavy-tirggered tables.
and it made me wonder - is there a way to tell how much time of backend
was spent on triggers, index updates and so on?
like:
total query time: 1 secons
trigger a: 0.50 second
trigger b: 0.25 second
index update: 0.1 second

something like this.

is it possible?
will it be ever possible?

hubert

-- 
hubert lubaczewski
Network Operations Center
eo Networks Sp. z o.o.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: profiling postgresql queries?
  2005-04-12 10:46 profiling postgresql queries? hubert lubaczewski <hubert.lubaczewski@eo.pl>
@ 2005-04-12 14:10 ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-04-12 14:18   ` Re: profiling postgresql queries? Alex Turner <armtuk@gmail.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Tom Lane @ 2005-04-12 14:10 UTC (permalink / raw)
  To: hubert.lubaczewski@eo.pl; +Cc: pgsql-performance

hubert lubaczewski <hubert.lubaczewski@eo.pl> writes:
> and it made me wonder - is there a way to tell how much time of backend
> was spent on triggers, index updates and so on?

In CVS tip, EXPLAIN ANALYZE will break out the time spent in each
trigger.  This is not in any released version, but if you're desperate
you could load up a play server with your data and test.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: profiling postgresql queries?
  2005-04-12 10:46 profiling postgresql queries? hubert lubaczewski <hubert.lubaczewski@eo.pl>
  2005-04-12 14:10 ` Re: profiling postgresql queries? Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-04-12 14:18   ` Alex Turner <armtuk@gmail.com>
  2005-04-12 14:45     ` Re: profiling postgresql queries? hubert lubaczewski <hubert.lubaczewski@eo.pl>
  0 siblings, 1 reply; 6+ messages in thread

From: Alex Turner @ 2005-04-12 14:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: hubert.lubaczewski@eo.pl; pgsql-performance

Speaking of triggers...

Is there any plan to speed up plpgsql tiggers?  Fairly simple
crosstable insert triggers seem to slow my inserts to a crawl.

Is the best thing just to write triggers in C (I really don't want to
put this stuff in the application logic because it really doesn't
belong there).

Alex Turner
netEconomist

On Apr 12, 2005 10:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> hubert lubaczewski <hubert.lubaczewski@eo.pl> writes:
> > and it made me wonder - is there a way to tell how much time of backend
> > was spent on triggers, index updates and so on?
> 
> In CVS tip, EXPLAIN ANALYZE will break out the time spent in each
> trigger.  This is not in any released version, but if you're desperate
> you could load up a play server with your data and test.
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: profiling postgresql queries?
  2005-04-12 10:46 profiling postgresql queries? hubert lubaczewski <hubert.lubaczewski@eo.pl>
  2005-04-12 14:10 ` Re: profiling postgresql queries? Tom Lane <tgl@sss.pgh.pa.us>
  2005-04-12 14:18   ` Re: profiling postgresql queries? Alex Turner <armtuk@gmail.com>
@ 2005-04-12 14:45     ` hubert lubaczewski <hubert.lubaczewski@eo.pl>
  0 siblings, 0 replies; 6+ messages in thread

From: hubert lubaczewski @ 2005-04-12 14:45 UTC (permalink / raw)
  To: Alex Turner <armtuk@gmail.com>; +Cc: pgsql-performance

On Tue, Apr 12, 2005 at 10:18:31AM -0400, Alex Turner wrote:
> Speaking of triggers...
> Is there any plan to speed up plpgsql tiggers?  Fairly simple
> crosstable insert triggers seem to slow my inserts to a crawl.

plpgsql is quite fast actually. if some triggers slow inserts too much,
i guess you should be able to spped them up with some performance review
of trigger code.

depesz

-- 
hubert lubaczewski
Network Operations Center
eo Networks Sp. z o.o.

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: profiling postgresql queries?
  2005-04-12 10:46 profiling postgresql queries? hubert lubaczewski <hubert.lubaczewski@eo.pl>
@ 2005-04-12 14:43 ` Michael Fuhr <mike@fuhr.org>
  2005-04-12 15:00   ` Re: profiling postgresql queries? hubert lubaczewski <hubert.lubaczewski@eo.pl>
  1 sibling, 1 reply; 6+ messages in thread

From: Michael Fuhr @ 2005-04-12 14:43 UTC (permalink / raw)
  To: hubert lubaczewski <hubert.lubaczewski@eo.pl>; +Cc: pgsql-performance

On Tue, Apr 12, 2005 at 12:46:43PM +0200, hubert lubaczewski wrote:
> 
> the problem is that both the inserts and updated operate on
> heavy-tirggered tables.
> and it made me wonder - is there a way to tell how much time of backend
> was spent on triggers, index updates and so on?
> like:
> total query time: 1 secons
> trigger a: 0.50 second
> trigger b: 0.25 second
> index update: 0.1 second

EXPLAIN ANALYZE in 8.1devel (CVS HEAD) prints a few statistics for
triggers:

EXPLAIN ANALYZE UPDATE foo SET x = 10 WHERE x = 20;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using foo_x_idx on foo  (cost=0.00..14.44 rows=10 width=22) (actual time=0.184..0.551 rows=7 loops=1)
   Index Cond: (x = 20)
 Trigger row_trig1: time=1.625 calls=7
 Trigger row_trig2: time=1.346 calls=7
 Trigger stmt_trig1: time=1.436 calls=1
 Total runtime: 9.659 ms
(6 rows)

8.1devel changes frequently (sometimes requiring initdb) and isn't
suitable for production, but if the trigger statistics would be
helpful then you could set up a test server and load a copy of your
database into it.  Just beware that because it's bleeding edge, it
might destroy your data and it might behave differently than released
versions.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: profiling postgresql queries?
  2005-04-12 10:46 profiling postgresql queries? hubert lubaczewski <hubert.lubaczewski@eo.pl>
  2005-04-12 14:43 ` Re: profiling postgresql queries? Michael Fuhr <mike@fuhr.org>
@ 2005-04-12 15:00   ` hubert lubaczewski <hubert.lubaczewski@eo.pl>
  0 siblings, 0 replies; 6+ messages in thread

From: hubert lubaczewski @ 2005-04-12 15:00 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-performance

On Tue, Apr 12, 2005 at 08:43:59AM -0600, Michael Fuhr wrote:
> 8.1devel changes frequently (sometimes requiring initdb) and isn't
> suitable for production, but if the trigger statistics would be
> helpful then you could set up a test server and load a copy of your
> database into it.  Just beware that because it's bleeding edge, it
> might destroy your data and it might behave differently than released
> versions.

great. this is exactly what i need. thanks for hint.

depesz

-- 
hubert lubaczewski
Network Operations Center
eo Networks Sp. z o.o.

Attachments:

  signature.asc (application/pgp-signature, 188B) download

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-04-12 15:00 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-12 10:46 profiling postgresql queries? hubert lubaczewski <hubert.lubaczewski@eo.pl>
2005-04-12 14:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-12 14:18   ` Alex Turner <armtuk@gmail.com>
2005-04-12 14:45     ` hubert lubaczewski <hubert.lubaczewski@eo.pl>
2005-04-12 14:43 ` Michael Fuhr <mike@fuhr.org>
2005-04-12 15:00   ` hubert lubaczewski <hubert.lubaczewski@eo.pl>

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