Re: Clustering using dblink

On Wed, May 28, 2003 at 01:19:18PM +0700, Yudha Setiawan wrote:
> Thank's for Dear Martijn van Oosterhout for your interesting
> to help me.

Hi. In the future when you post to a mailing list, please respond to the
mailing list.

> >> Anyway, have you done all the usual things for performace (tune buffers
> and
> >> sort_mem)?
> * Here it is my conf. (Please open my-Attachment)

No problem there.

> >> Umm, I don't know where you got the idea that putting it
> >> on another server would improve speed.
> * I've been thinking that split the Table or Schema to the another
> * Hardisk with a controler for each will increase the speed, we also
> * put some table or schema that's we use to join to different side. And
> * we've done it. It was increase the speed but just in a month.
> * my data is growing so fast, it's getting slow again, finaly i tried to split
> * the data to the another Server using dblink. I put the original data,trigger,
> * function and sequence on Server-2(Back-Server) and I make a View
> * table and its rule on Server-1(Front-Server) it's also good for my front-end
> * because my-application is just shooting IP on Server-1(Front-Server).
> * I've been using Giga-byte LAN-Card from Server-1 to Server-2 to pay for
> * View table ( i know view table will taking much resource).

I don't know exactly how much output to expect. According to your output
below, you don't get any output at all?

> 3. here it is the way we call / execute my function.
>     select * from d_master.pr_onhand_warehouse_standar('2003/02/01','CSATGR','AA','AA','AG','','','');
> explain analyze
>     explain analyze select * from d_master.pr_onhand_gudang_standar('2003/02/01','CSATGR','AA','AA','AG','','','')
> QUERY PLAN
> --------------------------------------------------
> Function Scan on pr_onhand_gudang_standar
> (cost=0.00..12.50 rows=1000 width=398) (actual time=439697.19..439697.19 rows=0 loops=1)
> Total runtime: 439697.23 msec
> --------------------------------------------------
> (2 row(s) affected)

Hmm, it's not going into the function itself. Can you run EXPLAIN ANALYZE on
the query as a single statement instead of as a function, so the explain
analyze shows the actual structure of the query.

Somewhere there is going to be a Seq Scan of a large table that's probably
missing an index. Incidentaly, your schema is an excellent example why
artificial primary keys can be a good thing.

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington