X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id A87D5D1B56C for ; Wed, 13 Aug 2003 19:19:20 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 55610-08 for ; Wed, 13 Aug 2003 16:17:44 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 6A87ED1B561 for ; Wed, 13 Aug 2003 16:19:08 -0300 (ADT) Received: from news.hub.org (host-64-117-224-194.altec1.com [64.117.224.194] (may be forged)) by news.hub.org (8.12.9/8.12.9) with ESMTP id h7DJJ8eo095360 for ; Wed, 13 Aug 2003 19:19:08 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h7DJ78aG092668 for pgsql-performance@postgresql.org; Wed, 13 Aug 2003 19:07:08 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Perfomance Tuning Date: Wed, 13 Aug 2003 15:07:04 -0400 Organization: Hub.Org Networking Services Lines: 54 Message-ID: <60r83ptm07.fsf@dev6.int.libertyrms.info> References: <200308130846.55991.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Rational FORTRAN, linux) Cancel-Lock: sha1:NDvTyYzsgECA+AHrbOkslw9/Yus= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/247 X-Sequence-Number: 3064 josh@agliodbs.com (Josh Berkus) writes: >> The other advantage (which I hinted to above) with raw disks is being able >> to optimize queries to take advantage of it. Informix is multithreaded >> and it will spawn off multiple "readers" to do say, a seq scan (and merge >> the results at the end). > > I like this idea. Has it ever been discussed for PostgreSQL? Hmmm > .... we'd need to see some tests demonstrating that this approach > was still a technical advantage given the improvements in RAID and > FS technology since Informix was designed. Ah, but this approach isn't so much an I/O optimization as it is a CPU optimization. If you have some sort of join against a big table, and do a lot of processing on each component, there might be CPU benefits from the split: create table customers( id customer_id, name character varying, other fields ); --- And we're a phone company with 8 millions of them... create table customer_status ( customer_id customer_id, status status_code ); create table customer_address ( customer_id customer_id, address_info... ); And then are doing: select c.id, sum(status), address_label(c.id), balance(c.id) from customers c, customer_status cs; We know there's going to be a SEQ SCAN against customers, because that's the big table. If I wanted to finish the query as fast as possible, as things stand now, and had 4 CPUs, I would run 4 concurrent queries, for 4 ranges of customers. The Really Cool approach would be for PostgreSQL to dole out customers across four processors, perhaps throwing a page at a time at each CPU, where each process would quasi-independently build up their respective result sets. -- let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];; Christopher Browne (416) 646 3304 x124 (land)