Re: [GENERAL] Doubts about oid
Jayadevan M escribió: I am an Oracle guy who is learning PostgreSQL. oid sounded a lot like rowid in Oracle. In Oracle, access by rowid is expected to be the fastest way of accessing a record, faster than even an index access followed by table access using the primary key. That was why I have this doubt about usage of oid being deprecated. Even if we use a sequence as PK (which is there in Oracle too), it is not as fast as access by rowid (I don't know if this applies to PostgreSQL's oid too). This is important when we use a cursors in an Oracle procedure (function in PostgreSQL) and loop through it and update specific records, when some conditions are met. Of course, that approach has its drawbacks -as in the case when row movement is enabled some maintenance activity moves the row to another location. I suppose you could use a cursor and then UPDATE ... WHERE CURRENT OF the cursor Another scenario is when we want to delete duplicate records in a table. You can use the ctid system column for this. This column represents the physical position of the row in the table, so it changes in many situations, for example during an UPDATE. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Doubts about oid
On Thu, Feb 18, 2010 at 11:08 PM, Scott Marlowe scott.marl...@gmail.com wrote: Oracle and postgres are definitely different here. There's really no equivalent to rowid in pgsql. oid has no special optimizations. An indexed PK of a serial is about as good as it gets, possibly clustered. access by CTID is the fastest it gets. I use it to do mass updates after selecting a large number of rows. I can guarantee nobody else is modifying those rows so i know it is safe. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Doubts about oid
On Wednesday 17 February 2010 8:13:51 pm Jayadevan M wrote: Hi, I was reading about oid and default configuration of PostgreSQL. A couple of doubts 1) Why is use of OIDS considered deprecated? Is there something else that can be used in place of oids for user tables? Sequences: http://www.postgresql.org/docs/8.4/interactive/sql-createsequence.html 2) Is there a performance impact if we keep the default default_with_oids to ON? Googling, I came across this - http://philmcrew.com/oid.html But most of the links given at that page were broken and the page itself did not provide a lot of information. The primary question that needs to be asked is what do you want to do with them? It is not so much a performance issue as an admin issue. OIDs where created for Postgres internal system use and leaked out to user space. As a result they have some shortcomings as detailed in the above article. Given that sequences are available as number generators, it was decided to encourage/force OIDs to be for internal system use only. That decision is set and using OIDs on user tables is setting yourself for future problems. Thanks, Jayadevan -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Doubts about oid
Hi, The primary question that needs to be asked is what do you want to do with them? It is not so much a performance issue as an admin issue. OIDs where created for Postgres internal system use and leaked out to user space. As a result they have some shortcomings as detailed in the above article. Given that sequences are available as number generators, it was decided to encourage/force OIDs to be for internal system use only. That decision is set and using OIDs on user tables is setting yourself for future problems. I am an Oracle guy who is learning PostgreSQL. oid sounded a lot like rowid in Oracle. In Oracle, access by rowid is expected to be the fastest way of accessing a record, faster than even an index access followed by table access using the primary key. That was why I have this doubt about usage of oid being deprecated. Even if we use a sequence as PK (which is there in Oracle too), it is not as fast as access by rowid (I don't know if this applies to PostgreSQL's oid too). This is important when we use a cursors in an Oracle procedure (function in PostgreSQL) and loop through it and update specific records, when some conditions are met. Of course, that approach has its drawbacks -as in the case when row movement is enabled some maintenance activity moves the row to another location. Another scenario is when we want to delete duplicate records in a table. Thanks for your reply, Regards, Jayadevan DISCLAIMER: The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect.
Re: [GENERAL] Doubts about oid
On Thu, Feb 18, 2010 at 8:46 PM, Jayadevan M jayadevan.maym...@ibsplc.com wrote: Hi, The primary question that needs to be asked is what do you want to do with them? It is not so much a performance issue as an admin issue. OIDs where created for Postgres internal system use and leaked out to user space. As a result they have some shortcomings as detailed in the above article. Given that sequences are available as number generators, it was decided to encourage/force OIDs to be for internal system use only. That decision is set and using OIDs on user tables is setting yourself for future problems. I am an Oracle guy who is learning PostgreSQL. oid sounded a lot like rowid in Oracle. In Oracle, access by rowid is expected to be the fastest way of accessing a record, faster than even an index access followed by table access using the primary key. That was why I have this doubt about usage of oid being deprecated. Even if we use a sequence as PK (which is there in Oracle too), it is not as fast as access by rowid (I don't know if this applies to PostgreSQL's oid too). This is important when we use a cursors in an Oracle procedure (function in PostgreSQL) and loop through it and update specific records, when some conditions are met. Of course, that approach has its drawbacks -as in the case when row movement is enabled some maintenance activity moves the row to another location. Another scenario is when we want to delete duplicate records in a table. Oracle and postgres are definitely different here. There's really no equivalent to rowid in pgsql. oid has no special optimizations. An indexed PK of a serial is about as good as it gets, possibly clustered. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Doubts about oid
Jayadevan M wrote: Hi, The primary question that needs to be asked is what do you want to do with them? It is not so much a performance issue as an admin issue. OIDs where created for Postgres internal system use and leaked out to user space. As a result they have some shortcomings as detailed in the above article. Given that sequences are available as number generators, it was decided to encourage/force OIDs to be for internal system use only. That decision is set and using OIDs on user tables is setting yourself for future problems. I am an Oracle guy who is learning PostgreSQL. oid sounded a lot like rowid in Oracle. In Oracle, access by rowid is expected to be the fastest way of accessing a record, faster than even an index access followed by table access using the primary key. That was why I have this doubt about usage of oid being deprecated. Even if we use a sequence as PK (which is there in Oracle too), it is not as fast as access by rowid (I don't know if this applies to PostgreSQL's oid too). This is important when we use a cursors in an Oracle procedure (function in PostgreSQL) and loop through it and update specific records, when some conditions are met. Of course, that approach has its drawbacks -as in the case when row movement is enabled some maintenance activity moves the row to another location. Another scenario is when we want to delete duplicate records in a table. well, postgres' OID's were never a direct row address of any sort. as the previous poster said, OID's were an internal identifier, and were never really meant for general use but their use was tolerated in earlier versions of postgres when there were things you couldn't do without them. Even in Oracle, I don't believe rowid bypasses indexes, its more like an implicit SERIAL PRIMARY KEY field. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Doubts about oid
Hi, Even in Oracle, I don't believe rowid bypasses indexes, its more like an implicit SERIAL PRIMARY KEY field. Well, I understand the point is not very relevant, since oid is not similar to rowid. In Oracle, index scans are bypassed if we use rowid. 1)Access by unique index SQL select * from myt where id=200; Execution Plan -- Plan hash value: 1325982734 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT| | 1 |65 | 1 (0)| 00:00: 01 | | 1 | TABLE ACCESS BY INDEX ROWID| MYT | 1 |65 | 1 (0)| 00:00: 01 | |* 2 | INDEX UNIQUE SCAN | MYDX | 1 | | 1 (0)| 00:00: 01 | 2) Access by rowid SQL select * from myt where rowid='AAAH9iAAEafADH'; ID -- NAME 200 REFCON$ Execution Plan -- Plan hash value: 4204525950 --- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --- | 0 | SELECT STATEMENT | | 1 |77 | 1 (0)| 00:00:0 1 | | 1 | TABLE ACCESS BY USER ROWID| MYT | 1 |77 | 1 (0)| 00:00:0 Regards, Jayadevan DISCLAIMER: The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect.
