Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!weretis.net!feeder1.news.weretis.net!news.solani.org!.POSTED!not-for-mail From: Mladen Gogala Newsgroups: comp.databases.postgresql Subject: Re: Is PostgreSQL good? Date: Fri, 24 Jun 2011 16:32:25 +0000 (UTC) Organization: solani.org Lines: 62 Message-ID: References: <1308640710.210659@proxy.dienste.wien.at> <1308738638.232318@proxy.dienste.wien.at> <1308911383.819034@proxy.dienste.wien.at> <96j8s5Ft0hU1@mid.individual.net> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Trace: solani.org 1308933145 13321 eJwFwQcBACAIBMBKsl6Iw5D+EbwzAaGvwqC2ttnDjk6R5GrdvllDQed6IFAaB9Vjxi+d8D4tEhFg (24 Jun 2011 16:32:25 GMT) X-Complaints-To: abuse@news.solani.org NNTP-Posting-Date: Fri, 24 Jun 2011 16:32:25 +0000 (UTC) User-Agent: Pan/0.133 (House of Butterflies) X-User-ID: eJwNyskVACEIBNGU2LqVcEaF/EMYLnX4r+BU3hUEA41+4nAI7HzVZgrTvTiH3/LMGp+mikac3O8HA+sQRQ== Cancel-Lock: sha1:xiQy5QQ+Fqyrwd9cdM/6DzskV50= X-NNTP-Posting-Host: eJwNxsEBwCAIA8CVihCC4wg1+4/Q3uvgaTmMRAYEvWB09y6d4fRztW25x+Xtga9Jnpai/lkxPzThEZ0= Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:152 On Fri, 24 Jun 2011 12:57:11 +0200, Hans Castorp wrote: > PG's ctid is very similar as Oracle's ROWID ( Unfortunately, it's only valid within a transaction. There is no permanent rowid in Postgres. This wouldn't be a problem in itself because one should never use it as a primary key. This only presents a problem because the lack of the permanent rowid is what prevents Postgres from allowing global indexes on the partitioned tables. If you do "another level of redirection" as the old joke says, you have to access block headers and possibly do another I/O to read the row itself, which makes things slower. In a large partitioned table, with hundreds of millions of records, every row counts and you need all the speed you can get. I created global indexes on a partitioned table using Sphinx. Sphinx requires a bigint primary key, which was available here and the index works well. However, I cannot enforce the primary key or have a globally unique key without a ROWID. If there an implementation of global indexes without the unique row identifier, I don't need it any more. What I am talking about is a table like this: news=# select count(*) from moreover_documents; count ----------- 181491106 (1 row) Table "moreover.moreover_documents" Column | Type | Modifiers ----------------------+-----------------------------+----------- document_id | bigint | not null dre_reference | bigint | not null headline | character varying(4000) | author | character varying(200) | url | character varying(1000) | rank | bigint | content | text | stories_like_this | character varying(1000) | internet_web_site_id | bigint | not null harvest_time | timestamp without time zone | valid_time | timestamp without time zone | keyword | character varying(200) | article_id | bigint | not null media_type | character varying(20) | source_type | character varying(20) | created_at | timestamp without time zone | autonomy_fed_at | timestamp without time zone | language | character varying(150) | Indexes: "moreover_documents_pkey" PRIMARY KEY, btree (document_id) Triggers: insert_moreover_trigger BEFORE INSERT ON moreover_documents FOR EACH ROW EXECUTE PROCEDURE moreover_insert_trgfn() Number of child tables: 10 (Use \d+ to list them.) I need, global index and global stats, as well as the optimizer capable of dealing with that. -- http://mgogala.byethost5.com