From: gayatri ganpaa <gayatri_ganpaa@yahoo.com>
To: Alvaro Herrera <alvherre@surnet.cl>
To: pgsql-admin@postgresql.org
Subject: Re: varying pagesize in postgresql
Date: Tue, 21 Jun 2005 21:10:52 -0700 (PDT)
Message-ID: <20050622041052.78812.qmail@web51504.mail.yahoo.com> (raw)
In-Reply-To: <20050621172200.GA19214@surnet.cl>
Thanks. Here is what exactly I am doing:
I am working on distributes spatial databases. I am trying to speed up join:
1. I am creating an external r*-tree index on the largest table
2. Then distributing the leaves of the r* tree on different nodes of a cluster(Each node has its own instance of the database) according to some hash function. Each node will ahve a few tuples of the first table and the tuples of the second table which overlap with the corresponding leaves.
3. Then I run Partition Based Spatial Merge Join algorithm on each node, which gives u the candidates for the join.
4. Then I use the distance function of postgis to find the actual join pairs, I run this query on each node and then concatenate the results of all nodes to get the join pairs.
IN the last step, when I am checking with the actual geometry, will there be any performance gain by increasing the pagesize??
My query for the last step would be:
select t1.gid, t2.gid from table1 as t1, table2 as t2 where distance(t1.the_geom, t2,the_geom)=0;
Any other help or ideas for speeding up the process, related to postgresql?
Thanks again,
gayatri.
Alvaro Herrera <alvherre@surnet.cl> wrote:
[reformatted for sanity]
> On Mon, Jun 20, 2005 at 11:09:36PM -0700, gayatri ganpaa wrote:
> > How do I change the page size in postgresql? I have the postgresql-7.4.3 version.
> > I am trying to create an r*-tree index on a spatial table with
> > around 100,000 rows. I just want to check if increasing the page
> > size would make the join of two tables faster. Are there any other
> > options I could use to make this faster? Would increasing the page
> > size help?
On Tue, Jun 21, 2005 at 11:58:44AM -0500, Jim C. Nasby wrote:
> pg_dumpall
> Change something in one of the header files (which is docummented
> somewhere...)
BLCKSZ, in src/include/pg_config_manual.h. I doubt it would make the
index faster anyway ... I think you could post the query, schema, and
EXPLAIN ANALYZE to pgsql-performance so that people can give you further
advice. Going forward, rtree is somewhat obsolete -- maybe try setting
up a GiST index instead (not sure if it would be faster than rtree.)
--
Alvaro Herrera ()
"God is real, unless declared as int"
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------------
Yahoo! Sports
Rekindle the Rivalries. Sign up for Fantasy Football
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: gayatri_ganpaa@yahoo.com, alvherre@surnet.cl
Subject: Re: varying pagesize in postgresql
In-Reply-To: <20050622041052.78812.qmail@web51504.mail.yahoo.com>
References: <20050621172200.GA19214@surnet.cl> <20050622041052.78812.qmail@web51504.mail.yahoo.com>
* 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