Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #324 > unrolled thread

Performance views/tables in PostgreSQL?

Back to article view | Back to comp.databases.postgresql

Contents

  Performance views/tables in PostgreSQL? Pol <eff.off.if.you.think.youre.getting.my.email@anon.com> - 2012-03-21 16:18 +0100
    Re: Performance views/tables in PostgreSQL? Mladen Gogala <gogala.mladen@gmail.com> - 2012-03-22 13:16 +0000
    Re: Performance views/tables in PostgreSQL? Jasen Betts <jasen@xnet.co.nz> - 2012-03-22 12:57 +0000
      Re: Performance views/tables in PostgreSQL? Mladen Gogala <gogala.mladen@gmail.com> - 2012-03-23 05:04 +0000

#324 — Performance views/tables in PostgreSQL?

Hi all,

I'm just wondering, what is the equivalent of the Oracle
Wait Interface (i.e. the V$<View_Name> system) in PostgreSQL?

TIA and rgs.


Paul...

[toc] | [next] | [standalone]

#325

On Wed, 21 Mar 2012 16:18:54 +0100, Pol wrote:

> Hi all,
> 
> I'm just wondering, what is the equivalent of the Oracle Wait Interface
> (i.e. the V$<View_Name> system) in PostgreSQL?
> 
> TIA and rgs.
> 
> 
> Paul...

Freeware Postgres doesn't have wait event interface. EnterpriseDB does 
have one, but it isn't free.. There are some internal tables which show 
statistics, the names start with "pg_".
One of the more useful ones is installed as extension:

gogala=# \d pg_stat_statements
          View "public.pg_stat_statements"
       Column        |       Type       | Modifiers 
---------------------+------------------+-----------
 userid              | oid              | 
 dbid                | oid              | 
 query               | text             | 
 calls               | bigint           | 
 total_time          | double precision | 
 rows                | bigint           | 
 shared_blks_hit     | bigint           | 
 shared_blks_read    | bigint           | 
 shared_blks_written | bigint           | 
 local_blks_hit      | bigint           | 
 local_blks_read     | bigint           | 
 local_blks_written  | bigint           | 
 temp_blks_read      | bigint           | 
 temp_blks_written   | bigint           | 

That is the basis for pg_statspack



-- 
http://mgogala.byethost5.com

[toc] | [prev] | [next] | [standalone]

#326

On 2012-03-21, Pol <eff.off.if.you.think.youre.getting.my.email@anon.com> wrote:

> I'm just wondering, what is the equivalent of the Oracle
> Wait Interface (i.e. the V$<View_Name> system) in PostgreSQL?

explain analyze is the most commonly used query performance metric.

I think there may be something else too.

-- 
⚂⚃ 100% natural

[toc] | [prev] | [next] | [standalone]

#327

On Thu, 22 Mar 2012 12:57:52 +0000, Jasen Betts wrote:


> explain analyze is the most commonly used query performance metric.
> 
> I think there may be something else too.

With all due respect, there is nothing like the Oracle wait interface in 
the free version of PostgreSQL. Nada. Zilch. 
Explain plan answers the question "how will Postgres execute my query". 
The wait interface answers the question "where is the time spent". Note 
that explain plan answers the question about the future and wait 
interface answers the question about the past. Explain plan cannot tell 
you whether your application was waiting for lock, resolving a deadlock 
or simply dealing with a slow disk. It may not even be a database problem 
at all. Java application may have a bug ,causing it to sleep and not wake 
up until kissed by a prince, which doesn't happen that frequently.
Oracle will tell you that it's waiting for the more data from SQL*Net, so 
you can start looking into the application. Postgres will also allow you 
to make that conclusion, but not directly. You will see no activity on 
the server and conclude that there is a problem with the application 
itself.



-- 
http://mgogala.byethost5.com

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web