pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedsplit partitioned table across several postgres servers
3+ messages / 2 participants
[nested] [flat]
* split partitioned table across several postgres servers
@ 2006-02-17 08:12 martial.bizel@free.fr
0 siblings, 1 reply; 3+ messages in thread
From: martial.bizel@free.fr @ 2006-02-17 08:12 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: split partitioned table across several postgres servers
@ 2006-02-17 14:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: martial.bizel@free.fr
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2006-02-17 14:49 UTC (permalink / raw)
To: martial.bizel@free.fr; +Cc: pgsql-performance
martial.bizel@free.fr writes:
> In fact, I don't know how to have explain plan of remote node.
You send it an EXPLAIN.
You can *not* use a view defined as you suggest if you want decent
performance --- the dblink functions will fetch the entire table
contents and the filtering will be done locally. You'll need to
pass the WHERE conditions over to the remote servers, which more
or less means that you have to give them to the dblink functions
as text.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: split partitioned table across several postgres servers
@ 2006-02-17 15:18 martial.bizel@free.fr
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: martial.bizel@free.fr @ 2006-02-17 15:18 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
Selon Tom Lane <tgl@sss.pgh.pa.us>:
> martial.bizel@free.fr writes:
> > In fact, I don't know how to have explain plan of remote node.
>
> You send it an EXPLAIN.
Please, Could you send me what to put at end of request :
select * from dblink('my_connexion', 'EXPLAIN select * from test where
number='1' ') as ........
I want to be sure that remote test is seen as partitioned object.
thanks a lot.
>
> You can *not* use a view defined as you suggest if you want decent
> performance --- the dblink functions will fetch the entire table
> contents and the filtering will be done locally. You'll need to
> pass the WHERE conditions over to the remote servers, which more
> or less means that you have to give them to the dblink functions
> as text.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-02-17 15:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-17 08:12 split partitioned table across several postgres servers martial.bizel@free.fr
2006-02-17 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-17 15:18 ` martial.bizel@free.fr
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