Re: [PERFORM] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

Στις Wednesday 19 January 2011 19:26:56 ο/η Tom Lane έγραψε: > Achilleas Mantzios writes: > > Anyway, i will repost the EXPLAIN plans by copying pasting the query, > > without the analyze part. > > Please show EXPLAIN ANALYZE, not just EXPLAIN, results. When > complaining that the planner did t

Re: [PERFORM] the XID question

On 1月19日, 下午10時39分, kevin.gritt...@wicourts.gov ("Kevin Grittner") wrote: > Filip Rembia*kowski wrote: > > 2011/1/19 Charles.Hou : > >> " select * from mybook" SQL command also increase the XID ? > > > Yes. Single SELECT is a transaction. Hence, it needs a transaction > > ID. > > No, not in recent

Re: [PERFORM] Migrating to Postgresql and new hardware

On 01/19/2011 05:09 PM, Lars wrote: Thanks for the reply! MyISAM was chosen back in 2000. I'm not aware of the reasoning behind this choice... Dell claims both the Samsung and the Pliant are safe to use. Below is a quote from the Pliant datasheet: "No Write Cache: Pliant EFDs deliver outstandi

Re: [PERFORM] the XID question

Kevin Grittner wrote: Or just test it in psql. BEGIN, run your query, look at pg_locks. If an xid has been assigned, you'll see it there in the transactionid column. You can easily satisfy yourself which statements grab an xid... That's a good way to double-check exactly what's happening, bu

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

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 / (3 * amount_of_data_to_read + > >>> effective_cache_size) = percentage NOT cached. ?That is, if

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

Robert Haas wrote: > On Thu, Nov 11, 2010 at 2:35 PM, Tom Lane wrote: > > Robert Haas writes: > >> Yeah. ?For Kevin's case, it seems like we want the caching percentage > >> to vary not so much based on which table we're hitting at the moment > >> but on how much of it we're actually reading. > >

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

Tom Lane wrote: > Mladen Gogala writes: > > Again, having an optimizer which will choose the plan completely > > accurately is, at least in my opinion, less important than having a > > possibility of manual control, the aforementioned "knobs and buttons" > > and produce the same plan for the sa

Re: [PERFORM] the XID question

Andres Freund wrote: > On Wednesday, January 19, 2011 07:06:58 PM Chris Browne wrote: >> A read-only transaction won't consume XIDs, but if you don't >> expressly declare it read-only, they're still liable to get >> eaten... > No. The Xid is generally only allocated at the first place a real > x

Re: [PERFORM] the XID question

On Wednesday, January 19, 2011 07:06:58 PM Chris Browne wrote: > kevin.gritt...@wicourts.gov ("Kevin Grittner") writes: > > Filip Rembia*kowski wrote: > >> 2011/1/19 Charles.Hou : > >>> " select * from mybook" SQL command also increase the XID ? > >> > >> Yes. Single SELECT is a transaction. Hence

Re: [PERFORM] the XID question

kevin.gritt...@wicourts.gov ("Kevin Grittner") writes: > Filip Rembia*kowski wrote: >> 2011/1/19 Charles.Hou : > >>> " select * from mybook" SQL command also increase the XID ? >> >> Yes. Single SELECT is a transaction. Hence, it needs a transaction >> ID. > > No, not in recent versions of Po

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

2011/1/19 Bruce Momjian > > FYI, we do have a documentation section about how to configure Postgres > for improved performance if you don't care about durability: > >http://developer.postgresql.org/pgdocs/postgres/non-durability.html > A sometime ago I wrote in my blog [1] (sorry but av

Re: [PERFORM] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

Achilleas Mantzios writes: > Anyway, i will repost the EXPLAIN plans by copying pasting the query, without > the analyze part. Please show EXPLAIN ANALYZE, not just EXPLAIN, results. When complaining that the planner did the wrong thing, it's not very helpful to see only its estimates and not r

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

Chris Browne wrote: > gentosa...@gmail.com (A B) writes: > > If you just wanted PostgreSQL to go as fast as possible WITHOUT any > > care for your data (you accept 100% dataloss and datacorruption if any > > error should occur), what settings should you use then? > > Use /dev/null. It is web scal

Re: [PERFORM] the XID question

Filip Rembia*kowski wrote: > 2011/1/19 Charles.Hou : >> " select * from mybook" SQL command also increase the XID ? > > Yes. Single SELECT is a transaction. Hence, it needs a transaction > ID. No, not in recent versions of PostgreSQL. There's virtual transaction ID, too; which is all that's

Re: [PERFORM] the XID question

2011/1/19 Charles.Hou : > what's the definetion of XID? XID == "Transaction ID". > " select * from mybook" SQL command also increase the XID ? Yes. Single SELECT is a transaction. Hence, it needs a transaction ID. greets, Filip -- Sent via pgsql-performance mailing list (pgsql-performance@po

Re: [PERFORM] the XID question

On 1月19日, 下午5時19分, "Charles.Hou" wrote: > after i backdb->dropdb->restoredb and then vacuum analy+full -> vacuum > freeze > > the XID had been increased by 4 billion in two weeks...is it noraml? > > what's the definetion of XID? > > " select * from mybook" SQL command also increase the XID ? > > r

[PERFORM] the XID question

after i backdb->dropdb->restoredb and then vacuum analy+full -> vacuum freeze the XID had been increased by 4 billion in two weeks...is it noraml? what's the definetion of XID? " select * from mybook" SQL command also increase the XID ? reference: http://www.postgresql.org/docs/9.0/static/routi

Re: [PERFORM] Migrating to Postgresql and new hardware

Thanks for the reply! MyISAM was chosen back in 2000. I'm not aware of the reasoning behind this choice... Dell claims both the Samsung and the Pliant are safe to use. Below is a quote from the Pliant datasheet: "No Write Cache: Pliant EFDs deliver outstanding write performance without any depen

Re: [PERFORM] Migrating to Postgresql and new hardware

> Are you going to RAID the SSD drives at all? Yes, I was thinking four drives in RAID 10 and a (hot) spare drive... > Of course this is based on my experience, and I have my fireproof suit since > I mentioned the word fusionIO :) Hehe FusionIO has some impressive stats! SSD in RAID10 provides r

Re: [PERFORM] Migrating to Postgresql and new hardware

> No idea what mysql thinks a shard is, but in PG we have read-only hot > standby's. I used sharding as an expression for partitioning data into several databases. Each user in the system is unaware of any other user. The user never accesses the private data of another user. Each user could in th

Re: [PERFORM] "NOT IN" substantially slower in 9.0.2 than 8.3.13 - NOT EXISTS runs fast in both 8.3.13 and 9.0.2

Στις Tuesday 18 January 2011 16:26:21 ο/η Mladen Gogala έγραψε: > This leads me to the conclusion that the queries differ significantly. > 8.3.3 mentions NOT hashed plan, I don't see it in 9.02 and the filtering > conditions look differently. Are you sure that the plans are from the > same quer