Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3C26F476279; Thu, 23 Jan 2003 05:12:34 -0500 (EST) Received: from localhost.localdomain (unknown [194.204.44.121]) by postgresql.org (Postfix) with ESMTP id 9192D475AE4; Thu, 23 Jan 2003 05:12:19 -0500 (EST) Received: from localhost.localdomain (localhost.localdomain [127.0.0.1]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id h0NABSHf002506; Thu, 23 Jan 2003 12:11:28 +0200 Received: (from hannu@localhost) by localhost.localdomain (8.12.5/8.12.5/Submit) id h0NAB87w002504; Thu, 23 Jan 2003 12:11:08 +0200 X-Authentication-Warning: localhost.localdomain: hannu set sender to hannu@tm.ee using -f Subject: Re: Terrible performance on wide selects From: Hannu Krosing To: Tom Lane Cc: Dann Corbit , Steve Crawford , pgsql-performance@postgreSQL.org, pgsql-hackers@postgreSQL.org In-Reply-To: <25622.1043281087@sss.pgh.pa.us> References: <25622.1043281087@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit Organization: Message-Id: <1043316668.2348.15.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 23 Jan 2003 12:11:08 +0200 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/871 X-Sequence-Number: 34481 Tom Lane kirjutas N, 23.01.2003 kell 02:18: > "Dann Corbit" writes: > > Why not waste a bit of memory and make the row buffer the maximum > > possible length? > > E.g. for varchar(2000) allocate 2000 characters + size element and point > > to the start of that thing. > > Surely you're not proposing that we store data on disk that way. > > The real issue here is avoiding overhead while extracting columns out of > a stored tuple. We could perhaps use a different, less space-efficient > format for temporary tuples in memory than we do on disk, but I don't > think that will help a lot. The nature of O(N^2) bottlenecks is you > have to kill them all --- for example, if we fix printtup and don't do > anything with ExecEvalVar, we can't do more than double the speed of > Steve's example, so it'll still be slow. So we must have a solution for > the case where we are disassembling a stored tuple, anyway. > > I have been sitting here toying with a related idea, which is to use the > heap_deformtuple code I suggested before to form an array of pointers to > Datums in a specific tuple (we could probably use the TupleTableSlot > mechanisms to manage the memory for these). Then subsequent accesses to > individual columns would just need an array-index operation, not a > nocachegetattr call. The trick with that would be that if only a few > columns are needed out of a row, it might be a net loss to compute the > Datum values for all columns. How could we avoid slowing that case down > while making the wide-tuple case faster? make the pointer array incrementally for O(N) performance: i.e. for tuple with 100 cols, allocate an array of 100 pointers, plus keep count of how many are actually valid, so the first call to get col[5] will fill first 5 positions in the array save said nr 5 and then access tuple[ptrarray[5]] next call to get col[75] will start form col[5] and fill up to col[75] next call to col[76] will start form col[75] and fill up to col[76] next call to col[60] will just get tuple[ptrarray[60]] the above description assumes 1-based non-C arrays ;) -- Hannu Krosing