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 3B4B23A3F2F for ; Tue, 4 Jan 2005 19:56:14 +0000 (GMT) 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 12351-08 for ; Tue, 4 Jan 2005 19:55:55 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1C7E23A3EF7 for ; Tue, 4 Jan 2005 19:55:55 +0000 (GMT) Received: from dyn-68-143.tor.dsl.tht.net (dyn-68-143.tor.dsl.tht.net [134.22.68.143]) by tht.net (Postfix) with ESMTP id DC3CB76A80; Tue, 4 Jan 2005 14:55:58 -0500 (EST) Subject: Re: query rewrite using materialized views From: Rod Taylor To: "Wager, Ryan D [NTK]" Cc: Postgresql Performance In-Reply-To: <8543375597A43D4D8388D75ACD32B9B301F72C1A@PDAWB06C.ad.sprint.com> References: <8543375597A43D4D8388D75ACD32B9B301F72C1A@PDAWB06C.ad.sprint.com> Content-Type: text/plain Date: Tue, 04 Jan 2005 14:54:22 -0500 Message-Id: <1104868462.37702.114.camel@home> Mime-Version: 1.0 X-Mailer: Evolution 2.0.3 FreeBSD GNOME Team Port Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/45 X-Sequence-Number: 9735 On Tue, 2005-01-04 at 13:26 -0600, Wager, Ryan D [NTK] wrote: > Rod, > I do this, PG gets forked many times, it is tough to find the max > number of times I can do this, but I have a Proc::Queue Manager Perl > driver that handles all of the copy calls. I have a quad CPU machine. > Each COPY only hits ones CPU for like 2.1% but anything over about 5 > kicks the load avg up. Sounds like disk IO is slowing down the copy then. > Ill get some explain analysis and table structures out there pronto. > > -----Original Message----- > From: Rod Taylor [mailto:pg@rbt.ca] > Sent: Tuesday, January 04, 2005 1:02 PM > To: Wager, Ryan D [NTK] > Cc: Postgresql Performance > Subject: Re: [PERFORM] query rewrite using materialized views > > > 1)the 250 million records are currently whipped and reinserted as a > > "daily snapshot" and the fastest way I have found "COPY" to do this > from > > a file is no where near fast enough to do this. SQL*Loader from > Oracle > > does some things that I need, ie Direct Path to the db files access > > (skipping the RDBMS), inherently ignoring indexing rules and saving a > > ton of time (Dropping the index, COPY'ing 250 million records, then > > Recreating the index just takes way too long). > > If you have the hardware for it, instead of doing 1 copy, do 1 copy > command per CPU (until your IO is maxed out anyway) and divide the work > amongst them. I can push through 100MB/sec using methods like this -- > which makes loading 100GB of data much faster. > > Ditto for indexes. Don't create a single index on one CPU and wait -- > send off one index creation command per CPU. > > > 2)Finding a way to keep this many records in a fashion that can be > > easily queried. I even tried breaking it up into almost 2800 separate > > tables, basically views of the data pre-broken down, if this is a > > working method it can be done this way, but when I tried it, VACUUM, > and > > the COPY's all seemed to slow down extremely. > > Can you send us EXPLAIN ANALYSE output for the slow selects and a little > insight into what your doing? A basic table structure, and indexes > involved would be handy. You may change column and table names if you > like. > > > -----Original Message----- > > From: pgsql-performance-owner@postgresql.org > > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Josh > Berkus > > Sent: Tuesday, January 04, 2005 12:06 PM > > To: pgsql-performance@postgresql.org > > Cc: Yann Michel > > Subject: Re: [PERFORM] query rewrite using materialized views > > > > Yann, > > > > > are there any plans for rewriting queries to preexisting > materialized > > > views? I mean, rewrite a query (within the optimizer) to use a > > > materialized view instead of the originating table? > > > > Automatically, and by default, no. Using the RULES system? Yes, you > > can > > already do this and the folks on the MattView project on pgFoundry are > > > working to make it easier. > > --