pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rod Taylor <pg@rbt.ca>
To: Wager, Ryan D [NTK] <Ryan.D.Wager@mail.sprint.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: query rewrite using materialized views
Date: Tue, 04 Jan 2005 14:02:18 -0500
Message-ID: <1104865338.37702.107.camel@home> (raw)
In-Reply-To: <8543375597A43D4D8388D75ACD32B9B301F72C19@PDAWB06C.ad.sprint.com>
References: <8543375597A43D4D8388D75ACD32B9B301F72C19@PDAWB06C.ad.sprint.com>

>   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.
> 
-- 




view thread (10+ messages)  latest in thread

Message-ID: <1104865338.37702.107.camel@home>
Permalink:  ../1104865338.37702.107.camel@home/
Also on:    postgresql.org/message-id/1104865338.37702.107.camel@home

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pg@rbt.ca, Ryan.D.Wager@mail.sprint.com
  Subject: Re: query rewrite using materialized views
  In-Reply-To: <1104865338.37702.107.camel@home>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox