Re: [PERFORM] Data Warehousing Tuning

Paul, > Has anyone experienced real performance gains by moving the pg_xlog > files? Yes. Both for data load and on OLTP workloads, this increased write performance by as much as 15%. However, you need to configure the xlog drive correctly, you can't just move it to a new disk.Make sure

Re: [PERFORM] Data Warehousing Tuning

Hi Paul, just some quick thoughts: > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Paul Johnson > Sent: Wednesday, July 06, 2005 10:16 AM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Data Warehousing Tuning > > > Questions ar

Re: [PERFORM] join and query planner

On Wed, 6 Jul 2005, Dario wrote: > > (first at all, sorry for my english) > Hi. >- Does "left join" restrict the order in which the planner must join > tables? I've read about join, but i'm not sure about left join... Yes. Reordering the outer joins can change the results in some cases which

[PERFORM] join and query planner

(first at all, sorry for my english) Hi. - Does "left join" restrict the order in which the planner must join tables? I've read about join, but i'm not sure about left join... - If so: Can I avoid this behavior? I mean, make the planner resolve the query, using statistics (uniqueness, data d

Re: [PERFORM] CURSOR slowes down a WHERE clause 100 times?

Niccolo Rigacci wrote: >Hi to all, > >I have a performace problem with the following query: > > BEGIN; > DECLARE mycursor BINARY CURSOR FOR >SELECT > toponimo, > wpt > FROM wpt_comuni_view >WHERE ( > wpt && > setSRID('BOX3D(4.83 36, 20.16 47.5)'::BOX3

[PERFORM] CURSOR slowes down a WHERE clause 100 times?

Hi to all, I have a performace problem with the following query: BEGIN; DECLARE mycursor BINARY CURSOR FOR SELECT toponimo, wpt FROM wpt_comuni_view WHERE ( wpt && setSRID('BOX3D(4.83 36, 20.16 47.5)'::BOX3D, 4326) ); FETCH ALL IN my

Re: [PERFORM] ALTER TABLE tabla ALTER COLUMN columna SET STATISTICS number

On Wed, Jul 06, 2005 at 04:49:21PM -0300, Dario wrote: > where is stored the value set by ALTER TABLE table_name ALTER COLUMN > column_name SET STATISTICS = [1-1000]? pg_attribute.attstattarget Example query: SELECT attrelid::regclass, attname, attstattarget FROM pg_attribute WHERE attstattarget

[PERFORM] ALTER TABLE tabla ALTER COLUMN columna SET STATISTICS number

¿where is stored the value set by ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS = [1-1000]? I've set this to 1000, and I didn't remember in which column (doh!). Is there any table to look? (I did 'grep "set stat" $PGDATA/pg_log/*' and found it, but may be there is a better way) I

Re: [PERFORM] Storing data and indexes in different disks

I'd say it's a little early to worry about a 10% performance increase when you don't have any redundancy. You might want to consider using more, cheaper SATA disks - with more spindles you may very well get better performance in addition to redundancy. Anyway, here's an optimization project I jus

[PERFORM] Storing data and indexes in different disks

Hi, I'm about to recommend a server model to a client, and I've read a in many places (including in this list) that storing indexes in one disk and the rest of the database in other disk might increase the overall performance of the system in about 10%. Making this only by a symbolic link

[PERFORM] Data Warehousing Tuning

Hi all, we have the following setup: - Sun V250 server - 2*1.3GHz Sparc IIIi CPU - 8GB RAM - 8*73GB SCSI drives - Solaris 10 - Postgres 8 Disks 0 and 1 are mirrored and contain the OS and the various software packages, disks 2-7 are configured as a 320GB concatenation mounted on /data, which is w

Re: [PERFORM] Heavy virtual memory usage on production system

Alexander Stanier <[EMAIL PROTECTED]> writes: > Looks as though there are several processes which are acquiring a load > of locks: 13 locks isn't "a load". I was worried about scenarios in which a single process might take hundreds or thousands of locks; it doesn't look like you have that.

Re: [PERFORM] Heavy virtual memory usage on production system

Looks as though there are several processes which are acquiring a load of locks: pid | count --+--- 3193 | 2 3192 | 9 3191 | 7 3190 | 3 3189 | 2 3188 | 3 3187 | 3 3186 | 3 3185 | 3 3184 | 3 3183 | 3 3182 |13 3181 | 3 3179 |10 3175

Re: [PERFORM] Heavy virtual memory usage on production system

Alexander Stanier <[EMAIL PROTECTED]> writes: > The problem happened again this morning and I took the chance to check > out the locking situation. The number of locks increased dramatically up > to over 1000, but they were all "AccessShareLocks" and all were granted. > The odd "RowExclusiveLock

Re: [PERFORM] Heavy virtual memory usage on production system

The problem happened again this morning and I took the chance to check out the locking situation. The number of locks increased dramatically up to over 1000, but they were all "AccessShareLocks" and all were granted. The odd "RowExclusiveLock" appeared but none persisted. On the basis that noth