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.
Ill get some explain analysis and table structures out there pronto.
-Original Message-
From: Rod Taylor [mailto:[EMAIL PROTECTED]
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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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.
--
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html