X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0281752812 for ; Wed, 22 Jun 2005 01:11:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05734-02 for ; Wed, 22 Jun 2005 04:11:01 +0000 (GMT) Received: from web51504.mail.yahoo.com (web51504.mail.yahoo.com [206.190.38.196]) by svr1.postgresql.org (Postfix) with SMTP id 6370E5288B for ; Wed, 22 Jun 2005 01:10:56 -0300 (ADT) Received: (qmail 78814 invoked by uid 60001); 22 Jun 2005 04:10:52 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=4J9fI7n7gh6lLxl/L0ZF5x05u+aji5zSrhc+NhC2dBqknbNgS41VvUElzovUyxJnKtULNZUhLELTjHxJhRLkWeSAGNhzg2kdJYFGaqaY5xzHJS5Jzy+D1BXHGNzYuQBqFA8nIbkAGiBG0nVhXyjVOiCoI2yewUtLGfyokJKnz8w= ; Message-ID: <20050622041052.78812.qmail@web51504.mail.yahoo.com> Received: from [137.30.122.46] by web51504.mail.yahoo.com via HTTP; Tue, 21 Jun 2005 21:10:52 PDT Date: Tue, 21 Jun 2005 21:10:52 -0700 (PDT) From: gayatri ganpaa Subject: Re: varying pagesize in postgresql To: Alvaro Herrera , pgsql-admin@postgresql.org In-Reply-To: <20050621172200.GA19214@surnet.cl> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-327773957-1119413452=:77134" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.694 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_20_30, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200506/162 X-Sequence-Number: 17917 --0-327773957-1119413452=:77134 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 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 --0-327773957-1119413452=:77134 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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 ANALYZ E 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 --0-327773957-1119413452=:77134--