X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4FF3D54208 for ; Tue, 10 May 2005 15:54:10 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43382-03 for ; Tue, 10 May 2005 18:54:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3A4DC540AF for ; Tue, 10 May 2005 15:54:06 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j4AIrwae023019; Tue, 10 May 2005 14:53:58 -0400 (EDT) To: Greg Stark Cc: molson@oceanconsulting.com, pgsql-performance@postgresql.org Subject: Re: Prefetch In-reply-to: <87r7gfq79k.fsf@stark.xeocode.com> References: <200505100652.51401.molson@oceanconsulting.com> <87r7gfq79k.fsf@stark.xeocode.com> Comments: In-reply-to Greg Stark message dated "10 May 2005 14:13:11 -0400" Date: Tue, 10 May 2005 14:53:58 -0400 Message-ID: <23018.1115751238@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/160 X-Sequence-Number: 12324 Greg Stark writes: > Actually forcing things to use indexes is the wrong direction to go if you're > trying to process lots of data and want to stream it off disk as rapidly as > possible. I would think about whether you can structure your data such that > you can use sequential scans. Agreed. > In your application that might be hard. It sounds like you would need more or > less one table per stock ticker which would really be hard to manage. Actually, in a previous lifetime I used to do pretty much the same stuff Matt is working on. The reason I suggested parallelizing is that what you want is usually not so much the 200day moving average of FOO, as the 200day moving averages of a whole bunch of things. If your input table contains time-ordered data for all those things, then a seqscan works out pretty well. > One thing you might look into is using the CLUSTER command. But postgres > doesn't maintain the cluster ordering so it would require periodically > rerunning it. If the desired sort order is time-based, it falls out pretty much for free in this application, because historical data doesn't change -- you are only interested in appending at the right. In said previous lifetime, we used Postgres for tracking our actual transactions, but we built a custom file format for storing the individual tick data. That's not stuff you need transactional semantics for; the historical data is what it is. Besides, you need to compress it as much as you can because there's always too much of it. Machines are faster and disk space cheaper than they were at the time, but I'd still question the wisdom of using a Postgres row for each daily bar, let alone finer-grain data. regards, tom lane