p figure what's going
wrong.
pg_buffercache, pg_stattuple come first to explore your cached data 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
nsion thus we are able to track them and fix
the planner/costestimate issues.
I don't see why PostgreSQL needs 'Hints' *in-core*.
--
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.
a) detoast - loading and decompression (complete array is detoasted)
> b) access
>
> if you has 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/
gt; people with the expertise to be able to do it - and fewer still who're
> paid to work on Pg so they have time to focus on it. Covering indexes
> with Pg's MVCC model seem to be particularly challenging, too.
There was a recent thread on -hackers about index with UNIQUEness
mized into
> an index condition. This should be better in 9.2 ...
Oops, maybe I shuffled with this
* xxx_pattern_ops indexes can now be used for simple equality 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.
ave a postgresql 8.3,isn't it ?
like 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
implementation/design of this feature would be
> much appreciated.
maybe 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/
Postgre
s, if you then run ANALYZE you
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.
n osm_point (cost=402.15..40465.85 rows=430
> width=218) (actual time=121.888..137.
>Recheck Cond: (tags @> '"tourism"=>"viewpoint"'::hstore)
>Filter: (('01030...'::geometry && geom) AND
> _st_contains('01030'::ge
n osm_point (cost=402.15..40465.85 rows=430
> width=218) (actual time=121.888..137.
>Recheck Cond: (tags @> '"tourism"=>"viewpoint"'::hstore)
>Filter: (('01030...'::geometry && geom) AND
> _st_contains('01030'::ge
ide DB, that are useful to
combine and use just as a 'this is normal behavior'. 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
--
S
hat changes anything.
it should be valuable to have the kernel version and also confirm the
same behavior happens with XFS.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-performance mailing l
logy at SQL-Query
>> level
>
> That is currently discussed in other threads, but it won't happen any time
> soon (a few years in the future, maybe).
at the SQL level, I don't see the immediate benefit given that the
feature is not implemented: SQL level stuff (planner hin
e of days:
>
> http://folk.uio.no/rafael/upgrade_to_9.1/server-2/memory-week.png
>
> The memory pattern is the same even when running only one postgres
> cluster in a server with enough memory.
>
> Any ideas about why this dramatic change in memory usage when the only
> thing ap
t;
> --
> 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 - Développement,
t; For example with a web application, you could use apache bench
> (https://httpd.apache.org/docs/2.0/programs/ab.html) or something like
> that.
I like Tsung: http://tsung.erlang-projects.org/
It is very efficient (you can achieve tens or hundreds of thousands
connections per core)
And you can
r shared_buffers, you should use pg_buffercache to see what's
happening and maybe change the value to something higher (2GB, 4GB,
...) . You can also just test and find the best size for your
application workload.
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Suppo
iovanni Mancuso*
> System Architect
> Babel S.r.l. - http://www.babel.it
> *T:* 06.9826.9600 *M:* 3406580739 *F:* 06.9826.9680
> P.zza S.Benedetto da Norcia, 33 - 00040 Pomezia (Roma)
> --
> CONFIDENZIALE: Questo messaggio ed i suoi allegati sono di carattere
> confidenziale per i destinatari in indirizzo.
> E' vietato l'inoltro non autorizzato a destinatari diversi da quelli
> indicati nel messaggio originale.
> Se ricevuto per errore, l'uso del contenuto e' proibito; si prega di
> comunicarlo al mittente e cancellarlo immediatamente.
>
>
> --
> 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 - Développement, Expertise et Formation
<>
-
Limit (cost=0.00..757.51 rows=200 width=1126) (actual
time=43.648..564.798 rows=200 loops=1)
-> Index Scan using source_id_id_idx on records
(cost=0.00..1590267.66 rows=419868 width=1126) (actual
time=43.631..564.7
ble memory usage and
take that into account.
>
> Best 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
Scan on "message_box_Idx"
> (cost=0.00..536.94 rows=28858 width=0) (actual time=1.743..1.743
> rows=20903 loops=113)
> Index Cond: (box_id = b.id)
> - Total runtime: 431520.186 ms
> + Total runtime: 6940.369 ms
>
> That's pretty od
2011/5/26 panam :
> 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.
systematically.
> Do the "pathological" query again
> => takes almost "forever" (didn't wait...)
>
> Replay the dump
> Cluster:
>
> cluster message_pkey on message;
>
> Do the "pathological" query again
> => speed is back (~3 secon
-> Bitmap Index Scan on "message_box_Idx"
> (cost=0.00..570.31 rows=30640 width=0) (actual time=2.170..2.170 rows=20249
> loops=115)"
> " Index Cond: (m2.box_id = $0)"
> " Buffers: shared hit=6708"
> "Total runtime: 179334.310 ms"
>
>
> So from my limited experience, the only significant difference I see is that
> the Hash Anti Join takes a lot more time in plan 2, but I do not understand
> why.
> Ideas somebody?
>
> Thanks
> panam
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Hash-Anti-Join-performance-degradation-tp4420974p4420974.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 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
in mind for vacuum/checkpoint
tasks: if you are able to know hot and cold data, then order it in the
segments of the relation. But making it work at the planner level
looks hard. I am not opposed to the idea, but no idea how to do it
right now.
> --
> Jim C. Nasby, Database Archite
table is
stuck at some XX MB and page are well reused.
>
> --
> 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 2ndQua
--
> 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
, I think that's beyond feasibility for current software/OSes.
maybe not :) mincore is available in many OSes, and windows have
options to get those stats too.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
> --
> Sent via pgsql-perform
and
> effective_cache_size ought to supply, but I don't think it does, quite.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscript
0 loops=1)
>> -- -> Index Scan using connections_locked_node_idx on
>> connections_locked (cost=0.00..8.29 rows=1 width=16) (actual
>> time=0.024..0.024 rows=0 loops=1)
>> -- Index Cond: (node = 1015)
>> -- Fil
MD
> PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
> "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
&g
r boot time
parameters. (maybe it supports only K or M, so 512*1024...)
> 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 ma
2011/4/22 Tory M Blue :
> On Fri, Apr 22, 2011 at 9:45 AM, Cédric Villemain
> 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
2011/4/22 Cédric Villemain :
> 2011/4/22 Tory M Blue :
>> On Fri, Apr 22, 2011 at 4:03 AM, Cédric Villemain
>> wrote:
>>> 2011/4/21 Tory M Blue :
>>>> On Thu, Apr 21, 2011 at 7:27 AM, Merlin Moncure wrote:
>>>>> On Thu, Apr 21, 2011 at
2011/4/22 Tory M Blue :
> On Fri, Apr 22, 2011 at 4:03 AM, Cédric Villemain
> wrote:
>> 2011/4/21 Tory M Blue :
>>> On Thu, Apr 21, 2011 at 7:27 AM, Merlin Moncure wrote:
>>>> On Thu, Apr 21, 2011 at 3:28 AM, Tory M Blue wrote:
>>>
>>>>>
ommit_limit, and you probably want 32GB :)
Maybe you have some minor changes in your install or application usage
and you just hit the limit.
--
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
st to set based on a script because they won't have
the mixed sources of disk/memory, only the disk acces cost. (if
ANALYZE OSCACHE is good enough)
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
--
Sent vi
it is exactly what you are looking for, but linux do
provide access 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, Fo
h an Areca controller and some ssds to see how it
> goes.
>
> Also note that there is a funky interaction with an MSA70 and SSDs. they do
> not work together. (I'm not sure if HP's official branded ssd's have the
> same issue).
>
> The write degra
ential for optimization?
>
There are probably room for improvements, without more thinking, I
would suggest:
* review bufferstrategy to increase the buffer size for the pool when
there is a lot of free buffers
* have a bgwriter working just behind the seqscan (and probably a
biger pool of buffer
C functionality
> from ZFS in one block device based caching layer.
Bcache looks more interesting, yes. Still it is not production ready
and get some dangerous caveeat with administration tasks (for example
remounting devices without their caches open the door of all evils).
--
Cédric Ville
ber us needing to ask about on
> multiple occasions, I put back -- provisionally. If someone thinks
> they're pointless, I won't worry about them being dropped again:
> time zone, character encoding scheme, character set, and collation.
> I'm pretty sure I've seen
tp://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
> http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
>
>
--
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
> to pg_stat.tmp. pg_stat.tmp is around 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
--
;
> Mladen Gogala
> Sr. Oracle DBA
> 1500 Broadway
> New York, NY 10036
> (212) 329-5251
> www.vmsinfo.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/
2011/1/27 Andres Freund :
> 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 dep
2011/1/27 Michael Kohl :
> Cédric, thanks a lot for your answer so far!
>
> On Thu, Jan 27, 2011 at 12:24 PM, Cédric Villemain
> wrote:
>
>> you have swap used, IO on the swap partition ?
>
> Memory-wise we are fine.
>
>> can you paste the /proc/meminfo
uby on Rails applications" [2].
do you monitor the 'locks' ? and the commit/rollbacks ?
>
> Thanks,
> Michael
>
> [1]
> http://www.linux.com/learn/tutorials/394523-configuring-postgresql-for-pretty-good-performance
> [2] http://www.pgcon.org/2010/schedule
www.postgresql.org/docs/9.0/static/sql-altertable.html (and
index too, 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.
gt; Late reply, but one idea is to have the executor store hit counts for
> later use by the optimizer. Only the executor knows how many pages it
> had to request from the kernel for a query. Perhaps getrusage could
> tell us how often we hit the disk.
AFAIK getrusage does not provide
2011/1/20 Robert Haas :
> On Thu, Jan 20, 2011 at 4:17 AM, Cédric Villemain
> 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 f
2011/1/19 Bruce Momjian :
> Tom Lane wrote:
>> Robert Haas writes:
>> > On Fri, Nov 12, 2010 at 4:15 AM, Cédric Villemain
>> > wrote:
>> >>> I wondering if we could do something with a formula like 3 *
>> >>> amount_of_data_to_read /
y allocations, changing
> it won't help if good plans are already being chosen.
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
2010/11/12 Tom Lane :
> Robert Haas writes:
>> On Fri, Nov 12, 2010 at 4:15 AM, Cédric Villemain
>> wrote:
>>>> I wondering if we could do something with a formula like 3 *
>>>> amount_of_data_to_read / (3 * amount_of_data_to_read +
>>>> effe
2010/11/12 Vitalii Tymchyshyn :
> 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 Vi
e, and we are goign
to request those 15%.
>
> Best 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
that as a
> potential problem case. Our current system - where we essentially
> assume that the caching percentage is uniform across the board - can
> have the same problem in less artificial cases.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The E
regards, tom lane
>
> --
> 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
n output says otherwise, but the seq_scan stat value for the table
> kinda correlates.
Starting with 9.0, the contrib module pg_stat_statements provide a lot
of information about buffer access (from shared buffers usage, but
still very valuable information) you should have a look at it if you
have suc
2010/11/2 hubert depesz lubaczewski :
> On Tue, Nov 02, 2010 at 12:04:42PM +0100, Cédric Villemain wrote:
>> 2010/11/2 hubert depesz lubaczewski :
>> > On Mon, Nov 01, 2010 at 02:57:56PM +0100, Cédric Villemain wrote:
>> >> > CONSTRAINT tablei
2010/11/2 hubert depesz lubaczewski :
> 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"
>>
so be helpful if someone can send comprehensive libpq programming
> guide for PG 9.x. Online doc of libpq is not much helpful for a newbie like
> me.
>
>
> Best Regards,
> Divakar
>
>
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/
cution can't finish.
>>
>> Do you have lots of triggers on the table? Or foreign key relationships
>> that're DEFERRABLE ?
>>
>> --
>> Craig Ringer
>
>
>
--
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
stgresql.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://www.postgresql.org/mailpref/pgsql-performance
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-performance@postgresql.org)
T
ablespace | members_idx
> indexdef | CREATE UNIQUE INDEX email_website_unq ON members USING
> btree (emailaddress, websiteid)
>
>
> This table has also been vacuumed analyzed as well:
>
> select * from pg_stat_all_tables where relname = 'members';
> -[ RECORD 1 ]+---
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 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 pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
d run and after some time you get
>> rewarded with nice pictures! :-)
>
> Yes. I've intentionally sized it at 90% precisely so that I am
> reading as well as writing, which is what an actual production
> environment will resemble.
>
> --
> Sent via pgsql-performance
; verschiedenen Datenbanksystemen abstrahiert,
> Queries von Applikationen trennt und automatisch die Query-Ergebnisse
> auswerten kann.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.post
2010/5/28 Konrad Garus :
> 2010/5/27 Cédric Villemain :
>
>> 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 thinking
before everything was running this fast, so now that the system performs
> differently, maybe it will help?
yes. the documentation 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 ses
2010/5/27 Konrad Garus :
> 2010/5/27 Cédric Villemain :
>
>> 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 the code is needed for 9.0
&
2010/5/27 Konrad Garus :
> 2010/5/27 Cédric Villemain :
>
>> 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, that is the projection of
2010/5/27 Konrad Garus :
> 2010/5/27 Cédric Villemain :
>
>> 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 impa
plicit ordered join but I admit I haven't read
the whole thread (in particular the table size).
Ho, and I set statistics to a highter value for column category_id,
table station_category (seeing the same resquest and explain analyze
without date in the query will help)
>
> Dave
>
2010/5/27 Konrad Garus :
> 2010/5/26 Cédric Villemain :
>
>> 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, it is not
estion - is there a tool or built-in statistic that tells
> when/how often/how much a table is read from disk? I mean physical
> read, not poll from OS cache to shared_buffers.
>
> --
> Konrad Garus
>
> --
> Sent via pgsql-performance mailing list (pgsql-perform
2010/5/1 Cédric Villemain :
> 2010/4/28 Robert Haas :
>> On Mon, Apr 26, 2010 at 5:33 AM, Cédric Villemain
>> 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
2010/4/28 Robert Haas :
> On Mon, Apr 26, 2010 at 5:33 AM, Cédric Villemain
> 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, but it doesn&
writing:
>
> SELECT * FROM t2 JOIN t1 ON t1.t = t2.t WHERE t2.id = 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.
&g
2010/4/28 Robert Haas :
> On Mon, Apr 26, 2010 at 5:33 AM, Cédric Villemain
> 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, but it doesn&
gt; time=0.017..0.534 rows=100 loops=1)
> -> Seq Scan on t1 (cost=0.00..1.06 rows=1 width=16) (actual
> time=0.005..0.008 rows=1 loops=1)
> Filter: (t = 4)
> -> Index Scan using t_idx on t2 (cost=0.00..26.18 rows=93
> width=16) (actual time=0.007..0
2010/4/23 Robert Haas :
> On Fri, Apr 23, 2010 at 9:09 AM, Cédric Villemain
> wrote:
>> 2010/4/23 Robert Haas :
>>> On Thu, Apr 22, 2010 at 10:37 PM, Vlad Arkhipov
>>> wrote:
>>>> I don't think this is just an issue with statistics, because the
ing 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/mailpref/pgsql-performance
ee if it does change
something in your IO load.
Anyway it looks like it is centos 5 relative so what is your curernt
running kernel ? (and what FS )
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> htt
core project. Getting the
information from the OS is actualy a bit slow but possible. I try to
find time to finish my patch in order to get the info in the
pg_statio_* views :)
>
> ...Robert
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make c
ting up a new system, it's probably worth going for
> 8.4.2. Postgres is relatively easy to build from source.
>
> HTH,
>
> Richard
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http
The above shows what comes from shared_buffers versus the OS, however.
> > And if reads are all buffered, they are not coming from disk. Only
> > those that come from the OS _may_ have come from disk.
>
> I think he meant pg's shared_buffers not the OS kernel cache.
&g
sid are usually the same with a 15 days delay.
I strongly suggets to have a debian lenny 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
thing; it just depends on what you need.
>
> --
> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation | fax: 732.331.1301
> 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED]
> Edison, NJ 08837 | http://www.enterprisedb.com/
--
Cédric Villema
http://bucardo.org/check_postgres/ but it only supervise afaik
you can collect data 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.
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
rows=12 loops=1) Index Cond: (guid > 116505531)
> Filter: (deleted IS NULL)
>-> Index Scan using idx_rt_trap_param_trap_guid on
> rt_trap_param tp (cost=0.00..18.36 rows=89 width=79) (actual
> time=0.006..0.009 rows=1 loops=12) Index Cond: (rt_trap.guid
hp?/archives/21-8.3-vs.-8.2-a-simple-benchmark.html
[2] http://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)-----
l?
I 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=A
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
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 down-
Theo Kramer a écrit :
On Wed, 2007-10-10 at 17:00 +0200, Cédric Villemain wrote:
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://www.post
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
1 - 100 of 101 matches
Mail list logo
