Re: [PERFORM] Query hangs sometimes

W > > Something I can do ? Something I can check for ? > > //Bill > -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Blocking every 20 sec while mass copying.

) -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Slow counting on v9.3

relname = 'NewsArticle'; This is not the same. This one uses precomputed statistics, and doesn't scan the actual table data. But I'd like to add conditions so I don't like the last method. -- Yours sincerely, Kai Sellgren -- Guillaume Cottenceau -- Sent via pgsql-performance mailing

Re: [PERFORM] Postgresql performance degrading... how to diagnose the root cause

or upgrade to 9.x). -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Postgresql performance degrading... how to diagnose the root cause

while rewriting the table. Otherwise, VACUUM VERBOSE on both the established DB and a backup/restore on a fresh DB also provide a helpful comparison of how many pages are used for suspected tables. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance

[PERFORM] 9.2.1 index-only scans : abnormal heap fetches after VACUUM FULL

..192.520 rows=200 loops=1) Index Cond: (ca = 1) Heap Fetches: 0 Total runtime: 240.918 ms (4 rows) DROP TABLE ta; DROP TABLE -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

[PERFORM] non index use on LIKE on a non pattern string

be the best approach? Mine looks a bit ugly. Thanks, -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Error while vacuuming

failed: ERROR:  duplicate key value violates unique constraint c DETAIL:  Key (indexrelid)=(2678) already exists. We are using Postgres 9.0.1 Can you please help us out in understanding the cause of this error? Regards, Bhakti -- Guillaume Cottenceau -- Sent via pgsql

Re: [PERFORM] Performance problem with a table with 38928077 record

. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] : Performance Improvement Strategy

' AND nspname = 'public' ORDER BY relpages DESC; relkind = 'i' for indexes. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Show_database_bloat reliability? [was: Re: [PERFORM] REINDEX not working for wastedspace]

| 0.6 |0 ... A few investigations show that when tbloat is close to 1.0 then it seems not reliable, otherwise it seems useful. pg 8.4.7 -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes

Re: [PERFORM] The good, old times

02:48 (7/7): postgresql90-se (68%) 44% [= ] 7.0 kB/s | 2.2 MB 06:33 ETA 7 kilobytes per second??? That brings back the times of the good, old 9600 USR modems and floppy disks. What's your point and in what is it related to that ML? -- Guillaume Cottenceau -- Sent via pgsql

Re: [PERFORM] Query uses incorrect index

compared to your CPU. Even if some queries will run faster from a side-effect of these settings, you're likely to create other random problems... -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences

will end up with the same results and be even faster than any use of PostgreSQL. If anyone needs data, then just say you had data corruption, and that since 100% dataloss is accepted, then all's well. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences

Marti Raudsepp marti 'at' juffo.org writes: On Fri, Nov 5, 2010 at 13:11, Guillaume Cottenceau g...@mnc.ch wrote: Don't use PostgreSQL, just drop your data, you will end up with the same results and be even faster than any use of PostgreSQL. If anyone needs data, then just say you had data

Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences

worthwhile, while previous ones are now seen as useless. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Strange performance degradation

VERBOSE table; [...] INFO: table: found 0 removable, 64977 nonremovable row versions in 628 pages In that 628/4395 example, we have 85% bloat in production. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] PG 8.3 and server load

the currently running queries: SELECT procpid, datname, current_query, query_start FROM pg_stat_activity WHERE current_query 'IDLE' That may also be interesting. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] PG 8.3 and server load

Phoenix Kiula phoenix.kiula 'at' gmail.com writes: Tasks: 568 total,   1 running, 537 sleeping,   6 stopped,  24 zombie The stopped and zombie processes look odd. Any reason for these? -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] Bundling postgreSQL with my Java application

for. Specifically, you are probably looking for autovacuum to be enabled. autovacuum is enabled by default on PG 8.3 as well. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] How would you store read/unread topic status?

problems if queries are produced faster than consumed in the long run. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Censorship

Matthew Wakeling matthew 'at' flymine.org writes: It appears that I am being censored. Do you seriously think that censorman would kill your previous mails, but would let a It appears that I am being censored mail go through? -- Guillaume Cottenceau -- Sent via pgsql-performance mailing

Re: [PERFORM] Query much slower when run from postgres function

. -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] scheduling autovacuum at lean hours only.

was shared with untuned FSM and friends). -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] scheduling autovacuum at lean hours only.

these figures at all) Of course, these are good for us (bloat is very, very low and performance impact is not experienced in production), not necessarily for you. You should conduct your own tests. Be sure to also consider http://developer.postgresql.org/~wieck/vacuum_cost/ -- Guillaume Cottenceau

Re: [PERFORM] server space increasing very fast but transaction are very low

). -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Choosing a filesystem

? -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [PERFORM] limit clause breaks query planner?

rows may be at the beginning or at the end of the heap? Ref: [1] or even 1, as ANALYZE doesn't sample all the rows? -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36 -- Sent via pgsql

Re: [PERFORM] limit clause breaks query planner?

that (potentially) less rows are to be actually used from the inner resultset, so a different plan may be devised. -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36 -- Sent via pgsql-performance

Re: [PERFORM] limit clause breaks query planner?

. -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [PERFORM] which ext3 fs type should I use for postgresql

if the filesystem is still full (it needs some free disk space for its startup). Or maybe this has been fixed in recent versions? -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [PERFORM] which ext3 fs type should I use for postgresql

Matthew Wakeling matthew 'at' flymine.org writes: On Thu, 15 May 2008, Guillaume Cottenceau wrote: Also, IIRC when PG writes data up to a full filesystem, postmaster won't be able to then restart if the filesystem is still full (it needs some free disk space for its startup). Or maybe

Re: [PERFORM] which ext3 fs type should I use for postgresql

Joshua D. Drake jd 'at' commandprompt.com writes: Guillaume Cottenceau wrote: Matthew Wakeling matthew 'at' flymine.org writes: It is still relevant, as with 5% margin, you can afford changing that to 0% with tune2fs, just the time for you to start PG and remove some data by SQL

Re: [PERFORM] Vacuum settings

of bloat (IIRC, this VACUUM output is for 7.4, it has changed a bit since then) -- Guillaume Cottenceau -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] 7 hrs for a pg_restore?

(relpages)*8/1024 FROM pg_class, pg_namespace WHERE pg_namespace.oid = pg_class.relnamespace AND relkind = 'i' AND nspname = 'public'; ?column? -- 644 -- Guillaume Cottenceau ---(end of broadcast)--- TIP 2: Don't 'kill -9

Re: [PERFORM] SELECT * FROM table is too slow

for your running application(s). -- Guillaume Cottenceau, MNC Mobile News Channel SA ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Seq scans on indexed columns.

that 2 is more correct) [...] - Seq Scan on _user (cost=0.00..205537.72 rows=806972 width=24) -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36 ---(end of broadcast

[PERFORM] Cost-Based Vacuum Delay tuning

/~wieck/vacuum_cost/ -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating

Re: [PERFORM] Cost-Based Vacuum Delay tuning

% of dead tuples on large tables, and I witnessed quite some performance improvement while I could fix that. -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36 ---(end of broadcast

Re: [PERFORM] can't shrink relation

://archives.postgresql.org/pgsql-novice/2002-12/msg00126.php -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36 ---(end of broadcast)--- TIP 1: if posting/reading through

Re: [PERFORM] [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal

Michael Stone mstone+postgres 'at' mathom.us writes: On Tue, May 15, 2007 at 06:43:50PM +0200, Guillaume Cottenceau wrote: patch - basically, I think the documentation under estimates (or sometimes misses) the benefit of VACUUM FULL for scans, and the needs of VACUUM FULL if the routine

Re: [PERFORM] [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal

Jim C. Nasby decibel 'at' decibel.org writes: On Wed, May 16, 2007 at 09:41:46AM +0200, Guillaume Cottenceau wrote: [...] Come on, I don't suggest to remove several bold warnings about it, the best one being Therefore, frequently using VACUUM FULL can have an extremely negative effect

[PERFORM] [doc patch] a slight VACUUM / VACUUM FULL doc improvement proposal

a table containing almost only dead rows). /para para Ref: [1] http://archives.postgresql.org/pgsql-performance/2006-08/msg00419.php http://archives.postgresql.org/pgsql-performance/2007-05/msg00112.php -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av

Re: [PERFORM] estimating the need for VACUUM FULL and REINDEX

Guillaume Cottenceau gc 'at' mnc.ch writes: With that in mind, I've tried to estimate how much benefit would be brought by running VACUUM FULL, with the output of VACUUM VERBOSE. However, it seems that for example the removable rows reported by each VACUUM VERBOSE run is actually reused

Re: [PERFORM] estimating the need for VACUUM FULL and REINDEX

Heikki Linnakangas heikki 'at' enterprisedb.com writes: Guillaume Cottenceau wrote: According to documentation[1], VACUUM FULL's only benefit is returning unused disk space to the operating system; am I correct in assuming there's also the benefit of optimizing the performance of scans

Re: [PERFORM] truncate a table instead of vaccum full when count(*) is 0

this? It seems that you already know about the FSM stuff, according to your question about FSM and 8.3. You can also run VACUUM ANALYZE more frequently (after all, it doesn't lock the table). -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003

Re: [PERFORM] truncate a table instead of vaccum full when count(*) is 0

the occupied space is reused before a VACUUM is performed, or is something else happening? Maybe the FSM is only storing a reference to diskspages containing only dead rows, and that's the difference I've been missing? -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de

[PERFORM] estimating the need for VACUUM FULL and REINDEX

ORDER BY c2.relname; -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Query performance problems with partitioned tables

, in which case 3 partitions in the past must be scanned. -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36 ---(end of broadcast)--- TIP 9

Re: [PERFORM] Query performance problems with partitioned tables

(there is no direct way to see that timestamps in your 200704 partition are greater than timsteamp in your 200601 partition). I guess some sort of pg guru would be needed here to clarify things in a smart way, unlike me :) -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de

Re: [PERFORM] Cache hit ratio

because it has additional information, but I am not sure in which circumstances and the amount of better decisions it can take. -- Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36

Re: [PERFORM] really quick multiple inserts can use COPY?

(foo,bar) VALUES(+it.next()+,+CONST.BAR+);; } You should try to wrap that into a single transaction. PostgreSQL waits for I/O write completion for each INSERT as it's implicitely in its own transaction. Maybe the added performance would be satisfactory for you. -- Guillaume Cottenceau Create your

Re: [PERFORM] Poor performance on very simple query ?

for table bar CREATE TABLE foo=# insert into bar (baz) values (''); INSERT 217426996 1 foo=# insert into bar (baz) values (''); ERROR: duplicate key violates unique constraint bar_pkey -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch

Re: [PERFORM] Poor performance on seq scan

the kernel to synchronize a write and waiting until it is finished). Same can probably happen to the sync command. -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/ ---(end of broadcast)--- TIP 6: explain

Re: [PERFORM] Poor performance on seq scan

a look at this chapter: http://www.postgresql.org/docs/7.4/interactive/runtime-config.html#RUNTIME-CONFIG-QUERY -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/ ---(end of broadcast)--- TIP 5: don't

Re: [PERFORM] unsubscribe me

for unsubscribing. -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?

of the disk cache (that is, the portion of the kernel's disk cache that will be used for PostgreSQL data files). This is measured in disk pages, which are normally 8192 bytes each. The default is 1000. -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http

[PERFORM] increasing shared buffers: how much should be removed from OS filesystem cache?

all, memory added to shared buffers should be mecanically removed from effective cache size (or others), so I cannot just increase it until the OS cannot cache anymore :) -- Guillaume Cottenceau ---(end of broadcast)--- TIP 4: Have you searched our

[PERFORM] perf pb solved only after pg_dump and restore

] Processes were always showing one/some postmaster on SELECT, a constant load of 1, and vmstat always showing activity in IO blocks out (application generate all sort of typical statements, some SELECT, UPDATE, INSERT either directly or through stored procedures) -- Guillaume Cottenceau

Re: [PERFORM] perf pb solved only after pg_dump and restore

Hi Markus, Thanks for your message. Guillaume Cottenceau wrote: We noticed a slowdown on our application while traffic was kinda heavy. The logics after reading the docs commanded us to trim the enlarged tables, run VACUUM ANALYZE and then expect fast performance again; but it wasn't

Re: [PERFORM] perf pb solved only after pg_dump and restore

Guillaume, Thanks for your help. On 28 Aug 2006 11:43:16 +0200, Guillaume Cottenceau [EMAIL PROTECTED] wrote: max_fsm_pages is 2 max_fsm_relations is 1000 Do they look low? Yes they are probably too low if you don't run VACUUM on a regular basis and you have a lot of UPDATE

Re: [PERFORM] perf pb solved only after pg_dump and restore

, the index access is extremely slow because of all the deleted entries the index scan has to skip. I see. However, from the additional information you gave above, I doubt it was index bloat. [...] -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http

Re: [PERFORM] perf pb solved only after pg_dump and restore

module at least since 7.4, and included in the server since 8.1). If you think that vacuum during working hours puts too much load on your server, there are options to tweak that, at least in 8.1. Ok, thanks. Unfortunately production insists on sticking on 7.4.5 for the moment :/ -- Guillaume

Re: [PERFORM] Postgresql Performance on an HP DL385 and

-- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] Speed Up Offset and Limit Clause

. If my query is: SELECT * FROM table ORDER BY id, name OFFSET 5 LIMIT 1 It takes about 2 seconds. First you should read the appropriate documentation. http://www.postgresql.org/docs/8.1/interactive/performance-tips.html -- Guillaume Cottenceau ---(end

Re: [PERFORM] planner with index scan cost way off actual cost,

Jim C. Nasby jnasby 'at' pervasive.com writes: On Tue, Mar 21, 2006 at 02:03:19PM +0100, Guillaume Cottenceau wrote: Jim C. Nasby jnasby 'at' pervasive.com writes: On Tue, Mar 21, 2006 at 10:40:45PM +1200, Mark Kirkwood wrote: I was going to recommend higher - but not knowing what

Re: [PERFORM] planner with index scan cost way off actual cost,

Hi Scott, Scott Marlowe smarlowe 'at' g2switchworks.com writes: On Wed, 2006-03-22 at 02:04, Guillaume Cottenceau wrote: [...] Yes, we use 7.4.5 actually, because it just works, so production wants to first deal with all the things that don't work before upgrading. I have recently

Re: [PERFORM] planner with index scan cost way off actual cost, advices to tweak cost constants?

this is rather new/fresh for me, I have no idea how smart that choice is (but based on my general feeling about pg, I'm suspecting this is actually smart but I am not smart enough to see why ;p). -- Guillaume Cottenceau ---(end of broadcast)--- TIP 5

Re: [PERFORM] Postmaster using only 4-5% CPU

very large figures in the await field compared to other servers using raid1 controllers, that's my best guess, but I was unable to find why and how to fix (and the vendor has been very helpless until now). I'm wondering if we don't have an issue with the driver but have no more clue. -- Guillaume

Re: [PERFORM] planner with index scan cost way off actual cost, advices to tweak cost constants?

Guillaume, Thanks for your answer. On 17 Mar 2006 11:09:50 +0100, Guillaume Cottenceau wrote: Reading the documentation and postgresql list archives, I have run ANALYZE right before my tests, I have increased the statistics target to 50 for the considered table; my problem

[PERFORM] planner with index scan cost way off actual cost, advices to tweak cost constants?

', on an ext3 partition with data=ordered, and run Linux 2.6.12. -- Guillaume Cottenceau ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings