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

and the block content. Or some weird database configuration ? (parameters in PostgreSQL can be set per DB, per role, etc...) -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally

Re: [PERFORM] Hints (was Poor performance using CTE)

*. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.

Re: [PERFORM] Performance of a large array access by position (tested version 9.1.3)

very large arrays, then @a is significant There is a place to add PG_GETARG_ARRAY_P_SLICE. The code is just not done yet. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description

Re: [PERFORM] index-only scan is missing the INCLUDE feature

. There was a recent thread on -hackers about index with UNIQUEness of some columns only. The objective was near the one you describe here. So you're not alone looking after that. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Re: [PERFORM] Tablespaces and query planning

may change them to be less precise. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.

Re: [PERFORM] Multiple Concurrent Updates of Shared Resource Counter

you can manage something around UNIQUE (license_id,license_seat_number). It depends of what you achieve, and the tables structures you have. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description

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

is equal to = in your case, since 8.4 Also you probably want to have a look at http://www.postgresql.org/docs/9.1/static/indexes-opclass.html about your index definition (add the text_pattern_ops when required) -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support

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

comparisons, not only for LIKE (Tom) -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

loops=1) Index Cond: (tags @ 'tourism=viewpoint'::hstore) Total runtime: 137.881 ms (6 rows) -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

loops=1) Index Cond: (tags @ 'tourism=viewpoint'::hstore) Total runtime: 137.881 ms (6 rows) -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] random_page_cost = 2.0 on Heroku Postgres

'. It turns to be easy in the long term to see if things go better or worse. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes

Re: [PERFORM] wal_level=archive gives better performance than minimal - why?

. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain +33 (0)6

Re: [PERFORM] PostgreSQL Parallel Processing !

the immediate benefit given that the feature is not implemented: SQL level stuff (planner hint) are here to workaround what the server can not handle on its own. And PostgreSQL policiy is not to allow planner hint, but to fix/improve the server. -- Cédric Villemain +33 (0)6 20 30 22 52 http

Re: [PERFORM] Dramatic change in memory usage with version 9.1

when the only thing apparently changed from our side is the postgres version? Thanks in advance for any help. Can you report what is filling the cache and the swap ? -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation

Re: [PERFORM] query uses index but takes too much time?

-much-time-tp5020742p5020742.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric

Re: [PERFORM] Benchmarking tools, methods

to build tsung scenario from its parsed log). You can add dynamic stuff in the xml (core function provided by tsung) and also write your own erland modules to add complexity to your scenario. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement

Re: [PERFORM] Optimize the database performance

. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

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

-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation logo.gif

Re: [PERFORM] Query with order by and limit is very slow - wrong index used

=0.00..1590267.66 rows=419868 width=1126) (actual time=43.631..564.700 rows=200 loops=1) Index Cond: ((source_id)::text = 'http://ebuw.uw.edu.pl/dlibra/oai-pmh-repository.xml'::text) Total runtime: 564.895 ms -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL

Re: [PERFORM] PostgreSQL-related topics of theses and seminary works sought (Was: Hash index use presently(?) discouraged...)

regards, Vitalii Tymchyshyn. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7

Re: [PERFORM] Hash Anti Join performance degradation

noticed that the query was long to be killed. I added a CHECK_FOR_INTERRUPT() in the for(;;) loop in nodeHashjoin.c. It fixes the delay when trying to kill but I don't know about performance impact this can have in this place of the code. -- Cédric Villemain               2ndQuadrant http

Re: [PERFORM] Hash Anti Join performance degradation

/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [PERFORM] Hash Anti Join performance degradation

2011/5/26 panam pa...@gmx.net: Hi all, Cédric Villemain-3 wrote: without explaining further why the antijoin has bad performance without cluster, I wonder why you don't use this query : SELECT  b.id,                   max(m.id) FROM box b, message m WHERE m.box_id = b.id GROUP BY b.id

Re: [PERFORM] Hash Anti Join performance degradation

://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

)                         http://jim.nasby.net -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise

Re: [PERFORM] [PERFORMANCE] expanding to SAN: which portion best to move

to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] Fill Factor

subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise

Re: [PERFORM] How to avoid seq scans for joins between union-all views (test case included)

://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL

Re: [PERFORM] reducing random_page_cost from 4 to 2 to force index scan

list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list

Re: [PERFORM] Benchmarking a large server

.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise

Re: [PERFORM] Benchmarking a large server

...) Thanks, Chris -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] oom_killer

. -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- 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] oom_killer

2011/4/22 Tory M Blue tmb...@gmail.com: On Fri, Apr 22, 2011 at 4:03 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2011/4/21 Tory M Blue tmb...@gmail.com: On Thu, Apr 21, 2011 at 7:27 AM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Apr 21, 2011 at 3:28 AM, Tory M Blue tmb

Re: [PERFORM] oom_killer

2011/4/22 Cédric Villemain cedric.villemain.deb...@gmail.com: 2011/4/22 Tory M Blue tmb...@gmail.com: On Fri, Apr 22, 2011 at 4:03 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2011/4/21 Tory M Blue tmb...@gmail.com: On Thu, Apr 21, 2011 at 7:27 AM, Merlin Moncure mmonc

Re: [PERFORM] oom_killer

2011/4/22 Tory M Blue tmb...@gmail.com: On Fri, Apr 22, 2011 at 9:45 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: CommitLimit:     4128760 kB Committed_AS:    2380408 kB Are you sure it is a PAE kernel ? You look limited to 4GB. Figured that the Commitlimit is actually

Re: [PERFORM] Linux: more cores = less concurrency.

to counters in: /sys/devices/system/node/node*/numastat I also find usefull to check meminfo per node instead of via /proc -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] Performance

the disk acces cost. (if ANALYZE OSCACHE is good enough) -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Intel SSDs that may not suck

be monitored looking at svctime from sar. We may be implementing that in the near future to detect when this creeps up again. svctime is untrustable. From the systat author, this field will be removed in a future version. -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr

Re: [PERFORM] buffercache/bgwriter

more than X% of free pages * add more :) I believe it should be ok to do good improvement for special case easely identifiable like yours. -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing

Re: [PERFORM] Anyone tried Flashcache with PostgreSQL?

ready and get some dangerous caveeat with administration tasks (for example remounting devices without their caches open the door of all evils). -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance

Re: [PERFORM] high user cpu, massive SELECTs, no io waiting problem

 :-) -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et

Re: [PERFORM] Write-heavy pg_stats_collector on mostly idle server

.                                      http://www.pgexperts.com -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Cédric Villemain

Re: [PERFORM] Write-heavy pg_stats_collector on mostly idle server

270K. An strace of the stats collector process shows that the stats collector is, in fact, rewriting the entire stats file twice per second. Anyone seen anything like this before? it is the expected behavior, IIRC -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr

Re: [PERFORM] Talking about optimizer, my long dream

: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] High load,

-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] High load,

2011/1/27 Michael Kohl michael.k...@tupalo.com: Cédric, thanks a lot for your answer so far! On Thu, Jan 27, 2011 at 12:24 PM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: you have swap used, IO on the swap partition ? Memory-wise we are fine. can you paste the /proc/meminfo

Re: [PERFORM] High load,

2011/1/27 Andres Freund and...@anarazel.de: On Thursday, January 27, 2011 12:24:10 PM Cédric Villemain wrote: maintenance_work_mem = 512MB 128MB is usualy enough Uhm, I don't want to be picky, but thats not really my experience. Sorts for index creation are highly dependent on a high m_w_m

Re: [PERFORM] Possible to improve query plan?

, but they already have a default at 90% for btree) -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http

Re: [PERFORM] anti-join chosen even when slower than old plan

2011/1/19 Bruce Momjian br...@momjian.us: Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Nov 12, 2010 at 4:15 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: I wondering if we could do something with a formula like 3 * amount_of_data_to_read / (3

Re: [PERFORM] anti-join chosen even when slower than old plan

2011/1/20 Robert Haas robertmh...@gmail.com: On Thu, Jan 20, 2011 at 4:17 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: I think his point is that we already have a proven formula (Mackert-Lohmann) and shouldn't be inventing a new one out of thin air. The problem is to figure

Re: [PERFORM] anti-join chosen even when slower than old plan

how often we hit the disk. AFAIK getrusage does not provide access to real IO counters but filesystem's ones. :-( -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Slow query + why bitmap index scan??

via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via

Re: [PERFORM] anti-join chosen even when slower than old plan

: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] anti-join chosen even when slower than old plan

://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] anti-join chosen even when slower than old plan

mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing

Re: [PERFORM] anti-join chosen even when slower than old plan

2010/11/12 Vitalii Tymchyshyn tiv...@gmail.com: 12.11.10 12:56, Cédric Villemain написав(ла): I supposed it was an answer to my mail but not sure... please keep CC'ed people, it is easier to follow threads (at least for me) OK 2010/11/12 Vitalii Tymchyshyntiv...@gmail.com: I'd say

Re: [PERFORM] anti-join chosen even when slower than old plan

2010/11/12 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: On Fri, Nov 12, 2010 at 4:15 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: I wondering if we could do something with a formula like 3 * amount_of_data_to_read / (3 * amount_of_data_to_read

Re: [PERFORM] questions regarding shared_buffers behavior

of information about buffer access (from shared buffers usage, but still very valuable information) you should have a look at it if you have such postgresql version installed. -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

Re: [PERFORM] Insert performance with composite index

2010/11/2 hubert depesz lubaczewski dep...@depesz.com: On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:   CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex) ) the index definition is CREATE INDEX PK_AT2   ON ABC   USING btree   (event, tableindex) TABLESPACE

Re: [PERFORM] Insert performance with composite index

2010/11/2 hubert depesz lubaczewski dep...@depesz.com: On Tue, Nov 02, 2010 at 12:04:42PM +0100, Cédric Villemain wrote: 2010/11/2 hubert depesz lubaczewski dep...@depesz.com: On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:   CONSTRAINT tableindex_pkey PRIMARY KEY

Re: [PERFORM] Insert performance with composite index

, Divakar -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- 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] How does PG know if data is in memory?

-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- 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] Massive update, memory usage

begin woring well, but after 100 seconds of executions increase usage of RAM, and then Swap and finally all RAM and swap are used and execution can't finish. Do you have lots of triggers on the table? Or foreign key relationships that're DEFERRABLE ? -- Craig Ringer -- Cédric Villemain

Re: [PERFORM] Periodically slow inserts

-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- 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 does PG know if data is in memory?

-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support -- 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] oracle to psql migration - slow query in postgres

last_autoanalyze | 2010-09-16 20:50:00.712418-04 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain               2ndQuadrant http://2ndQuadrant.fr

Re: [PERFORM] How does PG know if data is in memory?

Performance    Pre-ordering at: https://www.packtpub.com/postgresql-9-0-high-performance/book -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain

Re: [PERFORM] write barrier question

am reading as well as writing, which is what an actual production environment will resemble. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain

Re: [PERFORM] How to insert a bulk of data with unique-violations very fast

list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing

Re: [PERFORM] shared_buffers advice

2010/5/28 Konrad Garus konrad.ga...@gmail.com: 2010/5/27 Cédric Villemain cedric.villemain.deb...@gmail.com: Exactly. And the time to browse depend on the number of blocks already in core memory. I am interested by tests results and benchmarks if you are going to do some :) I am still

Re: [PERFORM] shared_buffers advice

2010/5/27 Konrad Garus konrad.ga...@gmail.com: 2010/5/26 Cédric Villemain cedric.villemain.deb...@gmail.com: At the moment where a block is requested for the first time (usualy 8kb from postgres, so in fact 2 blocks in OS), you have 'duplicate' buffers. But, depending of your workload

Re: [PERFORM] Random Page Cost and Planner

the same resquest and explain analyze without date in the query will help) Dave -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make

Re: [PERFORM] shared_buffers advice

2010/5/27 Konrad Garus konrad.ga...@gmail.com: 2010/5/27 Cédric Villemain cedric.villemain.deb...@gmail.com: It works thanks to mincore/posix_fadvise stuff : you need linux. It is stable enough in my own experiment. I did use it for debugging purpose in production servers with succes. What

Re: [PERFORM] shared_buffers advice

2010/5/27 Konrad Garus konrad.ga...@gmail.com: 2010/5/27 Cédric Villemain cedric.villemain.deb...@gmail.com: pgmincore() and pgmincore_snapshot() both are able to mmap up to 1GB. Does it mean they can occupy 1 GB of RAM? How does it relate to amount of page buffers mapped by OS? well

Re: [PERFORM] shared_buffers advice

2010/5/27 Konrad Garus konrad.ga...@gmail.com: 2010/5/27 Cédric Villemain cedric.villemain.deb...@gmail.com: well, that is the projection of file in memory. only projection, but the memory is still acquire. It is ok to rework this part and project something like 128MB and loop. (in fact

Re: [PERFORM] Random Page Cost and Planner

is fine for this topic : http://www.postgresql.org/docs/8.4/interactive/explicit-joins.html Consider the parameter to explicit join order (you can set it per sql session). You know your data and know what are the tables with less results to join first. ;) Dave -- Cédric Villemain

Re: [PERFORM] shared_buffers advice

mean physical read, not poll from OS cache to shared_buffers. -- Konrad Garus -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain

Re: [PERFORM] Optimization idea

2010/4/28 Robert Haas robertmh...@gmail.com: On Mon, Apr 26, 2010 at 5:33 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: In the first query, the planner doesn't use the information of the 2,3,4. It just does a : I'll bet I'll have 2 rows in t1 (I think it should say 3

Re: [PERFORM] Optimization idea

2010/5/1 Cédric Villemain cedric.villemain.deb...@gmail.com: 2010/4/28 Robert Haas robertmh...@gmail.com: On Mon, Apr 26, 2010 at 5:33 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: In the first query, the planner doesn't use the information of the 2,3,4. It just does a : I'll

Re: [PERFORM] Optimization idea

= X; SELECT * FROM t2 JOIN t1 ON t1.t = t2.t WHERE t2.t = X; side note : You might want/need to improve statistics in the column t2.t (in situation/distribution like this one) For me this is about 8x faster. ...Robert -- Cédric Villemain -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] Optimization idea

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

Re: [PERFORM] Optimization idea

) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain -- 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] Optimization idea

2010/4/23 Robert Haas robertmh...@gmail.com: On Fri, Apr 23, 2010 at 9:09 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2010/4/23 Robert Haas robertmh...@gmail.com: On Thu, Apr 22, 2010 at 10:37 PM, Vlad Arkhipov arhi...@dc.baikal.ru wrote: I don't think this is just

Re: [PERFORM] stats collector suddenly causing lots of IO

kernel ? (and what FS ) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Forcing index scan on query produces 16x faster

* views :) ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] Limited Shared Buffer Problem

source. HTH, Richard -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Cédric Villemain -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] Best suiting OS

and to backport newer packages if really required (like postgres 8.4). Debian come with good tools to achieve that (and there is debian-backport repository, sure) -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org signature.asc

Re: [PERFORM] Fusion-io ioDrive

Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/ -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org signature.asc Description

Re: [PERFORM] A guide/tutorial to performance monitoring and tuning

and monitor with munin : http://pgfoundry.org/projects/muninpgplugins/ -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org signature.asc Description: This is a digitally signed message part.

Re: [PERFORM] Bad prepare performance

Hi Martin, please CC the mailing-list, then others can repply ;) Cédric Villemain (13:59 2008-03-31): Le Monday 31 March 2008, Martin Kjeldsen a écrit : I've done the same query on a 8.2.5 database. The first one is prepared first and the other is executed directly. I understand why

Re: [PERFORM] Bad prepare performance

) Total runtime: 0.733 ms (11 rows) -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org signature.asc Description: This is a digitally signed message part.

Re: [PERFORM] More shared buffers causes lower performances

://www.kaltenbrunner.cc/blog/uploads/83b4shm.gif [3] http://people.openwide.fr/~gsmet/postgresql/tps_shared_buffers.png (X=shared_buffers in MB/Y=results with pgbench) -- Guillaume ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Cédric

Re: [PERFORM] Dual core Opterons beating quad core Xeons?

expect we will be running this hardware for 8.2, 8.3 and 8.4. Anyone aware of anything that might change the landscape for 8.4? -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org begin:vcard fn;quoted-printable:C=C3=A9dric

Re: [PERFORM] work_mem and shared_buffers

Bill Moran a écrit : On Fri, 9 Nov 2007 12:08:57 -0600 Campbell, Lance [EMAIL PROTECTED] wrote: How do you know when you should up the value of work_mem? Just play with the number. Is there a query I could do that would tell me if PostgreSql is performing SQL that could use more memory

Re: [PERFORM] Outer joins and Seq scans

Richard Huxton a écrit : Dimitri Fontaine wrote: Hi, Le lundi 29 octobre 2007, Tom Lane a écrit : Is there any chance you can apply the one-line patch shown here: http://archives.postgresql.org/pgsql-committers/2007-10/msg00374.php If rebuilding packages is not to your taste, possibly a

Re: [PERFORM] Performance problems with prepared statements

Theo Kramer a écrit : On Wed, 2007-10-10 at 17:00 +0200, Cédric Villemain wrote: snip Reading the manual, you can learn that prepared statement can (not) follow the same plan as direct query: the plan is make before pg know the value of the variable. See 'Notes' http

Re: [PERFORM] Performance problems with prepared statements

Theo Kramer a écrit : Hi I have been having some serious performance issues when using prepared statements which I can not re-produce when using a direct statement. Let me try to explain The query does an order by in descending order on several columns for which an index exists. The explain

Re: [PERFORM] Optimize slow query

tsearch2 an option for you ?) Thanks, Nuno Mariz ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Cédric Villemain Administrateur de Base de