Performance views/tables in PostgreSQL?

Performance views/tables in PostgreSQL?

Post by Pol » Fri, 23 Mar 2012 00:18:54



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

 
 
 

Performance views/tables in PostgreSQL?

Post by Mladen Gogal » Fri, 23 Mar 2012 22:16:15



> 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

 
 
 

Performance views/tables in PostgreSQL?

Post by Jasen Bett » Fri, 23 Mar 2012 21:57:52



Quote:> 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

 
 
 

Performance views/tables in PostgreSQL?

Post by Mladen Gogal » Sat, 24 Mar 2012 14:04:28



> 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

 
 
 

1. please help with converting a view in oracle into postgresql readably code

Hello,

I am trying to convert an application to postgresql, and am having a
bear of a time converting the following view (from oracle). What it
does, just in case you aren't very familiar with oracle syntax, is group
the average reg_state from the events_registrations table after having
converted the varchar variables present in reg_state, into numbers to
represent the various possibilities). Then, once it has found the
average number for all items in the table events_registrations with the
same order_id number it fills in a human readable word.

Thanks for any help in letting me either know how to create this view,
or to tell me how to convert the variables in the varchar field
reg_state into the numbers I want them to represent which I will be able
to manipulate with things such as floor() and avg().

Matthew Geddert
=====

create or replace view events_orders_states
as
select  o.*,
o_states.order_state
from events_orders o,
 (select
 order_id,
 decode (floor(avg (decode (reg_state,
                   'canceled', 0,
                   'waiting', 1,
                   'pending', 2,
                   'shipped', 3,
                   0))),
             0, 'canceled',
             1, 'incomplete',
             2, 'incomplete',
             3, 'fulfilled',
             'void') as order_state
 from events_registrations
 group by order_id) o_states
where o_states.order_id = o.order_id;

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

2. New Risc OS Browser

3. Problem with view in PostgreSQl 7.3

4. Forgotten password!!!

5. Simple view confuses PostgreSQL query planning

6. MIST, Pixels & Infocules

7. 5.0.1 vs 5.0.15: view performance

8. SMS install troubles

9. performance benefit with views?

10. View performance question

11. view and performance

12. Performance inside and outside view ( WAS Re: Select the

13. Set-Returning Functions WAS: On the performance of views