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 query? First the num of rows in the two portions are different so you might be comparing apples and oranges here. Anyway, i will repost the EXPLAIN plans by copying pasting the query, without the analyze part. 8.3.13 Unique (cost=633677.56..633700.48 rows=1834 width=23) - Sort (cost=633677.56..633682.14 rows=1834 width=23) Sort Key: m.surname, (COALESCE(m.givenname, ''::character varying)), (COALESCE(m.midname, ''::character varying)), m.id - Hash Join (cost=630601.65..633578.15 rows=1834 width=23) Hash Cond: (ms.vslid = vsl.id) - Hash Join (cost=630580.33..633530.01 rows=2261 width=27) Hash Cond: (ms.marinerid = m.id) - Seq Scan on marinerstates ms (cost=0.00..2875.32 rows=4599 width=8) Filter: (((state)::text = 'Active'::text) AND ((starttime)::date = '2007-01-11'::date) AND ((COALESCE(endtime, now()))::date = '2006-07-15'::date)) - Hash (cost=630491.54..630491.54 rows=7103 width=23) - Index Scan using mariner_pkey on mariner m (cost=628776.89..630491.54 rows=7103 width=23) Filter: ((NOT (hashed subplan)) AND ((marinertype)::text = 'Mariner'::text)) SubPlan - Unique (cost=0.00..628772.30 rows=1834 width=4) - Nested Loop (cost=0.00..628767.72 rows=1834 width=4) - Nested Loop (cost=0.00..627027.98 rows=1865 width=4) - Index Scan using marinerstates_marinerid on marinerstates msold (cost=0.00..626316.07 rows=2299 width=8) Filter: (((state)::text = 'Active'::text) AND ((starttime)::date = '2007-01-11'::date) AND ((COALESCE(endtime, now()))::date = '2006-07-15'::date) AND (subplan)) SubPlan - Bitmap Heap Scan on marinerstates msold2 (cost=4.28..12.11 rows=1 width=0) Recheck Cond: ((marinerid = $0) AND (starttime $2)) Filter: ((id $1) AND ((state)::text = 'Active'::text) AND (($2 - endtime) = '1 year 6 mons'::interval)) - Bitmap Index Scan on marinerstates_marinerid_starttime (cost=0.00..4.28 rows=2 width=0) Index Cond: ((marinerid = $0) AND (starttime $2)) - Index Scan using vessels_pkey on vessels vslold (cost=0.00..0.30 rows=1 width=4) Index Cond: (vslold.id = msold.vslid) - Index Scan using mariner_pkey on mariner mold (cost=0.00..0.92 rows=1 width=4) Index Cond: (mold.id = msold.marinerid) Filter: ((mold.marinertype)::text = 'Mariner'::text) - Hash (cost=17.81..17.81 rows=281 width=4) - Seq Scan on vessels vsl (cost=0.00..17.81 rows=281 width=4) (31 rows) 9.0.2 Unique (cost=11525.09..11571.55 rows=3717 width=23) - Sort (cost=11525.09..11534.38 rows=3717 width=23) Sort Key: m.surname, (COALESCE(m.givenname, ''::character varying)), (COALESCE(m.midname, ''::character varying)), m.id - Hash Join (cost=8281.98..11304.67 rows=3717 width=23) Hash Cond: (ms.marinerid = m.id) - Hash Join (cost=20.12..2963.83 rows=3717 width=4) Hash Cond: (ms.vslid = vsl.id) - Seq Scan on marinerstates ms (cost=0.00..2889.32 rows=4590 width=8) Filter: (((state)::text = 'Active'::text) AND ((starttime)::date = '2007-01-11'::date) AND ((COALESCE(endtime, now()))::date = '2006-07-15'::date)) - Hash (cost=16.72..16.72 rows=272 width=4) - Seq Scan on vessels vsl (cost=0.00..16.72 rows=272 width=4) - Hash (cost=8172.57..8172.57 rows=7143 width=23) - Seq Scan on mariner m (cost=7614.86..8172.57 rows=7143 width=23) Filter: ((NOT (hashed SubPlan 1)) AND ((marinertype)::text = 'Mariner'::text))
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 theory be assigned their own database server. This makes it easy to split the 4 users over a number of database servers. There are some shared data that is stored in a special shared database. The standby database is exactly the same as the master (save a bit of data that has not been synced yet.) I assume you know this... but I'd really recommend trying out PG's hot-standby and make sure it works the way you need (because I bet its different than mysql's). Assuming the shared and the sharded databases are totally different (lets call them database a and c), with the PG setup you'd have database a on one computer, then one master with database b on it (where all writes go), then several hot-standby's mirroring database b (that support read-only queries). As our data is easily partitioned into any number of servers we do not plan to use replication for load balancing. We do however plan to use it to set up a backup site. Its pretty hard to guess what your usage pattern is (70% read, small columns, no big blobs (like photos), etc)... and even then we'd still have to guess. It's more like 40% read 60% write. Not only will I not compare apples to oranges, but I really wont compare apples in Canada to oranges in Japan. :-) Hehe /Lars -- 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] 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 redundancy in case of disc failure. How do you handle this with fusionIO? Two mirrored cards? /Lars -- 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] 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 dependence on write cache and thus does not use battery/supercap. As others have mentioned, how are you going to be doing your shards? Hmm... shards might not have been a good word to describe it. I'll paste what I wrote in another reply: 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 theory be assigned their own database server. This makes it easy to split the 4 users over a number of database servers. There are some shared data that is stored in a special shared database. /Lars -Ursprungligt meddelande- Från: mark [mailto:dvlh...@gmail.com] Skickat: den 19 januari 2011 05:10 Till: Lars Kopia: pgsql-performance@postgresql.org Ämne: RE: [PERFORM] Migrating to Postgresql and new hardware Comments in line, take em for what you paid for em. -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- ow...@postgresql.org] On Behalf Of Lars Sent: Tuesday, January 18, 2011 3:57 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Migrating to Postgresql and new hardware Hi, We are in the process of moving a web based application from a MySql to Postgresql database. Our main reason for moving to Postgresql is problems with MySql (MyISAM) table locking. I would never try and talk someone out of switching but MyISAM? What version of MySQL and did you pick MyISAM for a good reason or just happened to end up there? We will buy a new set of servers to run the Postgresql databases. The current setup is five Dell PowerEdge 2950 with 2 * XEON E5410, 4GB RAM. PERC 5/I 256MB NV Cache, 4 * 10K Disks (3 in RAID 5 + 1 spare). One server is used for shared data. Four servers are used for sharded data. A user in the system only has data in one of the shards. There is another server to which all data is replicated but I'll leave that one out of this discussion. These are dedicated database servers. There are more or less no stored procedures. The shared database size is about 20GB and each shard database is about 40GB (total of 20 + 40 * 4 = 180GB). I would expect the size will grow 10%-15% this year. Server load might increase with 15%-30% this year. This setup is disk I/O bound. The overwhelming majority of sql statements are fast (typically single row selects, updates, inserts and deletes on primary key) but there are some slow long running (10min) queries. As new server we are thinking of PowerEdge R510, 1 * Xeon X5650, 24Gb RAM, H700 512MB NV Cache. One would think you should notice a nice speed improvement, ceteris paribus, since the X5650 will have -significantly- more memory bandwidth than the 5410s you are used to, and you are going to have a heck of a lot more ram for things to cache in. I think the H700 is a step up in raid cards as well but with only 4 disks your probably not maxing out there. Dell has offered two alternative SSDs: Samsung model SS805 (100GB Solid State Disk SATA 2.5). (http://www.plianttechnology.com/lightning_lb.php) Pliant model LB 150S (149GB Solid State Drive SAS 3Gbps 2.5). (http://www.samsung.com/global/business/semiconductor/products/SSD/Prod ucts_Enterprise_SSD.html) The Samsung ones seems to indicate that they have protection in the event of a power failure, and the pliant does not mention it. Granted I haven't done or seen any pull the plug under max load tests on either family, so I got nothing beyond that it is the first thing I have looked at with every SSD that crosses my path. Both are SLC drives. The price of the Pliant is about 2,3 times the price of the Samsung (does it have twice the performance?). One alternative is 5 servers (1 shared and 4 shards) with 5 Samsung drives (4 in RAID 10 + 1 spare). Another alternative would be 3 servers (1 shared and 2 shards) with 5 Pliant drives (4 in RAID 10 + 1 spare). This would be slightly more expensive than the first alternative but would be easier to upgrade with two new shard servers when it's needed. As others have mentioned, how are you going to be doing your shards? Anyone have experience using the Samsung or the Pliant SSD? Any information about degraded performance over time? Any comments on the setups? How would an alternative with 15K disks (6 RAID 10 + 1 spare, or even 10 RAID10 + 1 spare) compare? You still may find that breaking xlog out to its own logical drive (2 drives in raid 1) gives a speed improvement to the overall. YMMV - so tinker and find out before you go deploying. How would these alternatives compare in I/O performance
[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/routine-vacuuming.html -- 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] the XID question
On 1月19日, 下午5時19分, Charles.Hou giveme...@gmail.com 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 ? reference:http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html sorry... not 4 billion , is 4 hundred million -- 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] the XID question
2011/1/19 Charles.Hou giveme...@gmail.com: 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] the XID question
Filip Rembia*kowskiplk.zu...@gmail.com wrote: 2011/1/19 Charles.Hou giveme...@gmail.com: 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 needed unless the transaction writes something. Also, as a fine point, if you use explicit database transactions (with BEGIN or START TRANSACTION) then you normally get one XID for the entire transaction, unless you use SAVEPOINTs. -Kevin -- 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] 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 scale, and there are good tutorials. But seriously, there *are* cases where blind speed is of use. When loading data into a fresh database is a good time for this; if things fall over, it may be pretty acceptable to start from scratch with mkfs/initdb. I'd: - turn off fsync - turn off synchronous commit - put as much as possible onto Ramdisk/tmpfs/similar as possible 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 -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] 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 ach...@matrix.gatewaynet.com 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 reality. 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
Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences
2011/1/19 Bruce Momjian br...@momjian.us 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 available only in pt-br) how to create an in-memory database with PostgreSQL. This little article is based on post of Mr. Robert Haas about this topic [2]. [1] http://fabriziomello.blogspot.com/2010/06/postgresql-na-memoria-ram-in-memory.html [2] http://rhaas.blogspot.com/2010/06/postgresql-as-in-memory-only-database_24.html -- Fabrízio de Royes Mello Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
Re: [PERFORM] the XID question
kevin.gritt...@wicourts.gov (Kevin Grittner) writes: Filip Rembia*kowskiplk.zu...@gmail.com wrote: 2011/1/19 Charles.Hou giveme...@gmail.com: 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 needed unless the transaction writes something. Also, as a fine point, if you use explicit database transactions (with BEGIN or START TRANSACTION) then you normally get one XID for the entire transaction, unless you use SAVEPOINTs. Erm, not *necessarily* in recent versions of PostgreSQL. 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... -- (format nil ~S@~S cbbrowne gmail.com) http://www3.sympatico.ca/cbbrowne/lisp.html Parenthesize to avoid ambiguity. -- 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] the XID question
On Wednesday, January 19, 2011 07:06:58 PM Chris Browne wrote: kevin.gritt...@wicourts.gov (Kevin Grittner) writes: Filip Rembia*kowskiplk.zu...@gmail.com wrote: 2011/1/19 Charles.Hou giveme...@gmail.com: 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 needed unless the transaction writes something. Also, as a fine point, if you use explicit database transactions (with BEGIN or START TRANSACTION) then you normally get one XID for the entire transaction, unless you use SAVEPOINTs. Erm, not *necessarily* in recent versions of PostgreSQL. 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 xid is needed. See GetCurrentTransactionId, AssignTransactionId in xact.c and the caller of the former. Andres -- 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] the XID question
Andres Freund and...@anarazel.de 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 xid is needed. See GetCurrentTransactionId, AssignTransactionId in xact.c and the caller of the former. 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 -Kevin -- 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] anti-join chosen even when slower than old plan
Tom Lane wrote: Mladen Gogala mladen.gog...@vmsinfo.com 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 same statement. More knobs and buttons is the Oracle way, and the end result of that process is that you have something as hard to use as Oracle. That's generally not thought of as desirable in this community. Let reply, but Mladen, you might want to look at my blog entry explaining why knobs are often not useful because they are only used by a small percentage of users (and confuse the rest): http://momjian.us/main/blogs/pgblog/2009.html#January_10_2009 -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] anti-join chosen even when slower than old plan
Robert Haas wrote: On Thu, Nov 11, 2010 at 2:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com 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. Well, we could certainly take the expected number of pages to read and compare that to effective_cache_size. ?The thing that's missing in that equation is how much other stuff is competing for cache space. ?I've tried to avoid having the planner need to know the total size of the database cluster, but it's kind of hard to avoid that if you want to model this honestly. I'm not sure I agree with that. I mean, you could easily have a database that is much larger than effective_cache_size, but only that much of it is hot. Or, the hot portion could move around over time. And for reasons of both technical complexity and plan stability, I don't think we want to try to model that. It seems perfectly reasonable to say that reading 25% of effective_cache_size will be more expensive *per-page* than reading 5% of effective_cache_size, independently of what the total cluster size is. 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. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] anti-join chosen even when slower than old plan
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 * amount_of_data_to_read + effective_cache_size) = percentage NOT cached. ?That is, if we're reading an amount of data equal to effective_cache_size, we assume 25% caching, and plot a smooth curve through that point. ?In the examples above, we would assume that a 150MB read is 87% cached, a 1GB read is 50% cached, and a 3GB read is 25% cached. But isn't it already the behavior of effective_cache_size usage ? No. 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 out what numbers to apply the M-L formula to. I've been thinking that we ought to try to use it in the context of the query as a whole rather than for individual table scans; the current usage already has some of that flavor but we haven't taken it to the logical conclusion. Is there a TODO here? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] 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, but it's not even that hard: gsmith=# select txid_current(); txid_current | 696 gsmith=# select 1; ?column? | 1 gsmith=# select 1; ?column? | 1 gsmith=# select txid_current(); txid_current | 697 Calling txid_current bumps the number up, but if you account for that you can see whether the thing(s) in the middle grabbed a real txid by whether the count increased by 1 or more than that. So here's what one that did get a real xid looks like: gsmith=# select txid_current(); txid_current | 702 gsmith=# insert into t(i) values(1); INSERT 0 1 gsmith=# select txid_current(); txid_current | 704 That proves the INSERT in the middle was assigned one. The commit message that added this feature to 8.3 has a good quick intro to what changed from earlier revs: http://archives.postgresql.org/pgsql-committers/2007-09/msg00026.php Don't have to actually read the source to learn a bit more, because it's actually documented! Mechanics are described at pgsql/src/backend/access/transam/README ; you need to know a bit more about subtransactions to follow all of it, but it gets the general idea across regardless: = Transaction and Subtransaction Numbering = Transactions and subtransactions are assigned permanent XIDs only when/if they first do something that requires one --- typically, insert/update/delete a tuple, though there are a few other places that need an XID assigned. If a subtransaction requires an XID, we always first assign one to its parent. This maintains the invariant that child transactions have XIDs later than their parents, which is assumed in a number of places. The subsidiary actions of obtaining a lock on the XID and and entering it into pg_subtrans and PG_PROC are done at the time it is assigned. A transaction that has no XID still needs to be identified for various purposes, notably holding locks. For this purpose we assign a virtual transaction ID or VXID to each top-level transaction. VXIDs are formed from two fields, the backendID and a backend-local counter; this arrangement allows assignment of a new VXID at transaction start without any contention for shared memory. To ensure that a VXID isn't re-used too soon after backend exit, we store the last local counter value into shared memory at backend exit, and initialize it from the previous value for the same backendID slot at backend start. All these counters go back to zero at shared memory re-initialization, but that's OK because VXIDs never appear anywhere on-disk. Internally, a backend needs a way to identify subtransactions whether or not they have XIDs; but this need only lasts as long as the parent top transaction endures. Therefore, we have SubTransactionId, which is somewhat like CommandId in that it's generated from a counter that we reset at the start of each top transaction. The top-level transaction itself has SubTransactionId 1, and subtransactions have IDs 2 and up. (Zero is reserved for InvalidSubTransactionId.) Note that subtransactions do not have their own VXIDs; they use the parent top transaction's VXID. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, 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: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] the XID question
On 1月19日, 下午10時39分, kevin.gritt...@wicourts.gov (Kevin Grittner) wrote: Filip Rembia*kowskiplk.zu...@gmail.com wrote: 2011/1/19 Charles.Hou giveme...@gmail.com: 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 needed unless the transaction writes something. my postgresql version is 8.1.3 you means the newer version has a virtual transaction ID. and what's the maxmium of this virtual id, also 4 billion ? should i also vacuum freeze the virtual id in the new version when it reached the 4 billion? Also, as a fine point, if you use explicit database transactions (with BEGIN or START TRANSACTION) then you normally get one XID for the entire transaction, unless you use SAVEPOINTs. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org) To make changes to your subscription:http://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
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 ach...@matrix.gatewaynet.com 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 reality. I did so two posts before but one more won't do any harm. Here we go: 9.0.2 - SLOW QUERY PLAN --- Unique (cost=11525.09..11571.55 rows=3717 width=23) (actual time=10439.797..10440.152 rows=603 loops=1) - Sort (cost=11525.09..11534.38 rows=3717 width=23) (actual time=10439.795..10439.905 rows=603 loops=1) Sort Key: m.surname, (COALESCE(m.givenname, ''::character varying)), (COALESCE(m.midname, ''::character varying)), m.id Sort Method: quicksort Memory: 71kB - Hash Join (cost=8281.98..11304.67 rows=3717 width=23) (actual time=10402.338..10438.875 rows=603 loops=1) Hash Cond: (ms.marinerid = m.id) - Hash Join (cost=20.12..2963.83 rows=3717 width=4) (actual time=0.228..35.178 rows=2625 loops=1) Hash Cond: (ms.vslid = vsl.id) - Seq Scan on marinerstates ms (cost=0.00..2889.32 rows=4590 width=8) (actual time=0.015..33.634 rows=2625 loops=1) Filter: (((state)::text = 'Active'::text) AND ((starttime)::date = '2007-01-11'::date) AND ((COALESCE(endtime, now()))::date = '2006-07-15'::date)) - Hash (cost=16.72..16.72 rows=272 width=4) (actual time=0.203..0.203 rows=272 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB - Seq Scan on vessels vsl (cost=0.00..16.72 rows=272 width=4) (actual time=0.004..0.117 rows=272 loops=1) - Hash (cost=8172.57..8172.57 rows=7143 width=23) (actual time=10402.075..10402.075 rows=12832 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 702kB - Seq Scan on mariner m (cost=7614.86..8172.57 rows=7143 width=23) (actual time=10386.549..10397.193 rows=12832 loops=1) Filter: ((NOT (hashed SubPlan 1)) AND ((marinertype)::text = 'Mariner'::text)) SubPlan 1 - Unique (cost=2768.00..7614.86 rows=1 width=4) (actual time=86.937..10385.379 rows=1454 loops=1) - Nested Loop (cost=2768.00..7614.86 rows=1 width=4) (actual time=86.936..10384.555 rows=1835 loops=1) Join Filter: (msold.marinerid = mold.id) - Index Scan using mariner_pkey on mariner mold (cost=0.00..1728.60 rows=14286 width=4) (actual time=0.007..14.250 rows=14286 loops=1) Filter: ((marinertype)::text = 'Mariner'::text) - Materialize (cost=2768.00..5671.97 rows=1 width=8) (actual time=0.003..0.328 rows=1876 loops=14286) - Nested Loop (cost=2768.00..5671.96 rows=1 width=8) (actual time=39.259..84.889 rows=1876 loops=1) - Hash Semi Join (cost=2768.00..5671.67 rows=1 width=12) (actual time=39.249..81.025 rows=1876 loops=1) Hash Cond: (msold.marinerid = msold2.marinerid) Join Filter: ((msold2.id msold.id) AND (msold2.starttime msold.starttime) AND ((msold.starttime - msold2.endtime) = '1 year 6 mons'::interval)) - Seq Scan on marinerstates msold (cost=0.00..2889.32 rows=4590 width=20) (actual time=0.003..33.964 rows=2625 loops=1) Filter: (((state)::text = 'Active'::text) AND ((starttime)::date = '2007-01-11'::date) AND ((COALESCE(endtime, now()))::date = '2006-07-15'::date)) - Hash (cost=2251.66..2251.66 rows=41307 width=24) (actual time=39.156..39.156 rows=41250 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 2246kB - Seq Scan on marinerstates msold2 (cost=0.00..2251.66 rows=41307 width=24) (actual
