Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query logging time, not values

Query logging time, not values

From
Ludwik Dylag
Date:
Hi
I have a database and ~150 clients non-stop writing to this database quite big pieces of text.
I have a performacne problem so I tried to increase log level, so I could see which queries take most time.
My postgresql.conf (Log section) is:

log_destination = 'stderr'
logging_collector = on
log_rotation_size = 1GB
log_connections = on
log_line_prefix = '%m %p %u %d %r '                     
log_lock_waits = on   
log_statement = 'ddl' 
log_temp_files = 4096 

And I got the query times + query parameters values, which makes my log extremly big.
How can I set the logging parameters to write query + duration time but without parameter values?

Thanks 
Ludwik
 




--
Ludwik Dyląg

position in DDL of columns used in indexes

From
Michael Gould
Date:

In other SQL engines that I've used, it is recommended that the columns that are used in various indexes be placed at the beginning of a row since at some point (depending on the engine and/or pagesize) wide rows could end up on other pages.  From a performance standpoint on large tables this makes a big difference.  Is the same true with Postgres.  Should I try and make sure that my indexes fit in the first 8192 bytes?

 

Bes Regards

Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax

Re: position in DDL of columns used in indexes

From
Matthew Wakeling
Date:
On Thu, 8 Oct 2009, Michael Gould wrote:
> In other SQL engines that I've used, it is recommended that the columns that are used in
> various indexes be placed at the beginning of a row since at some point (depending on the
> engine and/or pagesize) wide rows could end up on other pages.  From a performance
> standpoint on large tables this makes a big difference.  Is the same true with Postgres. 
> Should I try and make sure that my indexes fit in the first 8192 bytes?

Interesting question. AFAIK (I'm not an expert, someone correct me):

Postgres does not split rows across multiple pages, so this should never
be a concern. When a row is too big for a page, Postgres will select the
larger of the columns from the row and compress them. If that fails to
bring the row size down, then Postgres will select the larger columns and
remove them to a separate storage area, and leave just the references in
the actual row. Therefore, the order of columns should not matter.

Moreover, whether a row is used in an index should not make any
difference. The index stores the values too, right? Postgres will look up
in the index, and then fetch the rows, in two separate operations.

Matthew

--
 Let's say I go into a field and I hear "baa baa baa". Now, how do I work
 out whether that was "baa" followed by "baa baa", or if it was "baa baa"
 followed by "baa"?
         - Computer Science Lecturer

Re: position in DDL of columns used in indexes

From
Tom Lane
Date:
Matthew Wakeling <matthew@flymine.org> writes:
> Postgres does not split rows across multiple pages, so this should never
> be a concern. When a row is too big for a page, Postgres will select the
> larger of the columns from the row and compress them. If that fails to
> bring the row size down, then Postgres will select the larger columns and
> remove them to a separate storage area, and leave just the references in
> the actual row. Therefore, the order of columns should not matter.

> Moreover, whether a row is used in an index should not make any
> difference. The index stores the values too, right? Postgres will look up
> in the index, and then fetch the rows, in two separate operations.

Yeah.  There can be a small performance advantage to putting the more
frequently accessed columns first (so you don't have to skip over other
columns to get to them).  This has nothing directly to do with whether
they are indexed, though.

            regards, tom lane