Skip site navigation (1) Skip section navigation (2)

split partitioned table across several postgres servers

From: martial(dot)bizel(at)free(dot)fr
To: pgsql-performance(at)postgresql(dot)org
Subject: split partitioned table across several postgres servers
Date: 2006-02-17 08:12:39
Message-ID: 1140163959.43f585777f2cc@imp2-g19.free.fr (view raw or flat)
Thread:
Lists: pgsql-performance
Hello,

I want to split table partitioned across two servers postgres (two hosts).
To query this remote object, I want to make view with union on two servers with
two dblink.

But, How to be sure that optimizer plan on remote node is same than local node
(ie : optimizer scan only the selected partitions and not make full scan  of
the remote object) ?

example : server 1 (table test partionned on field number and 1 < number <10)
          server 2 (table test partitioned on field number 10 <number <20)

server 3 has view like :
create view remote_test
as
select * from dblink('conn_server1', select * from test) as test_server1(....)
union
select * from dblink('conn_server2', select * from test) as test_server2(....)

If I've made select on view remote_test like :
select * from remote_test where number<5 and number >15.

optimizer made full scan of all partitions on all servers or
scan only partition 1 to partition 4 on server1
and scan partiton 16 to partition 19 on server2
and union  ?

In fact, I don't know how to have explain plan of remote node.

Thanks a lot.

MB



In response to

Responses

pgsql-performance by date

Next:From: Jens-Wolfhard SchickeDate: 2006-02-17 08:18:39
Subject: Re: qsort again (was Re: [PERFORM] Strange Create Index
Previous:From: RonDate: 2006-02-17 06:20:58
Subject: Re: qsort again (was Re: [PERFORM] Strange Create

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group