PostgreSQL Performance - Query Feromance

Author

Query Feromance

Marco Furetto

2006-03-21, 3:34 am

Hello!

Can I Increment the perfomance of execution query?

Where is the instrument to analyze the query runnnig for create a Index
query for a single optimize that?

thank's

Marco "Furetto" Berri

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Reimer

2006-03-21, 3:34 am

Marco,

Could you give us the query you would like to improve performance?

----- Original Message -----
From: "Marco Furetto" <furetto@bluestudio.it>
To: <pgsql- performance@postgres
ql.org>
Sent: Monday, March 20, 2006 11:59 AM
Subject: [PERFORM] Query Feromance

> Hello!
>
> Can I Increment the perfomance of execution query?
>
> Where is the instrument to analyze the query runnnig for create a Index
> query for a single optimize that?
>
> thank's
>
> Marco "Furetto" Berri
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Marco Furetto

2006-03-21, 3:34 am

Hello!

I'm managing the db of a "Content Management environment" and I'm
searching for a "Query analyzer" to improve performance because i don't
know how many and what type of queries are executing on the system (for
the "where and join" block).

If i could have query's stats i could Optimize the queries indexes.

Thank's a lot!

Marco "Furetto" Berri

Reimer wrote:
> Marco,
>
> Could you give us the query you would like to improve performance?
>
>
> ----- Original Message ----- From: "Marco Furetto" <furetto@bluestudio.it>
> To: <pgsql- performance@postgres
ql.org>
> Sent: Monday, March 20, 2006 11:59 AM
> Subject: [PERFORM] Query Feromance
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Thomas Pundt

2006-03-21, 3:34 am

Hi,

On Tuesday 21 March 2006 09:25, Marco Furetto wrote:
| I'm managing the db of a "Content Management environment" and I'm
| searching for a "Query analyzer" to improve performance because i don't
| know how many and what type of queries are executing on the system (for
| the "where and join" block).

as a first step, I'd enable query duration logging; in postgresql.conf
I have set

log_min_duration_sta
tement = 3000

this will log each query that needs more than 3 seconds to complete.

The next step would be to "explain analyze" the problematic queries.

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Marco Furetto

2006-03-21, 7:28 am

ok, I enable query duration logging in postgresql.conf.

where is the instruments for analyze the statistics queries executing on
my db?

Eg.: Number of query executing, total time for executing a single query,
etc...

Thank's

Marco "Furetto" Berri

Thomas Pundt wrote:
> Hi,
>
> On Tuesday 21 March 2006 09:25, Marco Furetto wrote:
> | I'm managing the db of a "Content Management environment" and I'm
> | searching for a "Query analyzer" to improve performance because i don't
> | know how many and what type of queries are executing on the system (for
> | the "where and join" block).
>
> as a first step, I'd enable query duration logging; in postgresql.conf
> I have set
>
> log_min_duration_sta
tement = 3000
>
> this will log each query that needs more than 3 seconds to complete.
>
> The next step would be to "explain analyze" the problematic queries.
>
> Ciao,
> Thomas
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Thomas Pundt

2006-03-21, 7:28 am

Hi,

On Tuesday 21 March 2006 10:56, Marco Furetto wrote:
| ok, I enable query duration logging in postgresql.conf.
|
| where is the instruments for analyze the statistics queries executing on
| my _db?
|
| Eg.: Number of query executing, total time for executing a single query,
| etc...

I don't know if there are tools or settings available for PostgreSQL that do
such number-of-query-accounting; but you can set the
log_min_duration_sta
tement value to 0 to log all statements with their
duration.

See http://www.postgresql.org/docs/8.1/...ime-config.html
for more options on runtime configuration, especially 17.7 and 17.8
might be of interest for you.

Ciao,
Thomas

--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Marco Furetto

2006-03-22, 3:29 am

I find another java program for monitory application query:
http://www.p6spy.com/
with interface
http://www.jahia.net/jahia/page597.html

Thomas Pundt wrote:
> Hi,
>
> On Tuesday 21 March 2006 09:25, Marco Furetto wrote:
> | I'm managing the db of a "Content Management environment" and I'm
> | searching for a "Query analyzer" to improve performance because i don't
> | know how many and what type of queries are executing on the system (for
> | the "where and join" block).
>
> as a first step, I'd enable query duration logging; in postgresql.conf
> I have set
>
> log_min_duration_sta
tement = 3000
>
> this will log each query that needs more than 3 seconds to complete.
>
> The next step would be to "explain analyze" the problematic queries.
>
> Ciao,
> Thomas
>

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

Sponsored Links