pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
varying pagesize in postgresql
4+ messages / 3 participants
[nested] [flat]

* varying pagesize in postgresql
@ 2005-06-21 06:09 gayatri ganpaa <gayatri_ganpaa@yahoo.com>
  2005-06-21 16:58 ` Re: varying pagesize in postgresql Jim C. Nasby <decibel@decibel.org>
  0 siblings, 1 reply; 4+ messages in thread

From: gayatri ganpaa @ 2005-06-21 06:09 UTC (permalink / raw)
  To: pgsql-admin

Hi,
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?
 
Thanks,
gayatri.

__________________________________________________


		
---------------------------------
Yahoo! Sports
 Rekindle the Rivalries. Sign up for Fantasy Football

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: varying pagesize in postgresql
  2005-06-21 06:09 varying pagesize in postgresql gayatri ganpaa <gayatri_ganpaa@yahoo.com>
@ 2005-06-21 16:58 ` Jim C. Nasby <decibel@decibel.org>
  2005-06-21 17:22   ` Re: varying pagesize in postgresql Alvaro Herrera <alvherre@surnet.cl>
  0 siblings, 1 reply; 4+ messages in thread

From: Jim C. Nasby @ 2005-06-21 16:58 UTC (permalink / raw)
  To: gayatri ganpaa <gayatri_ganpaa@yahoo.com>; +Cc: pgsql-admin

pg_dumpall
Change something in one of the header files (which is docummented
somewhere...)
Recompile, install, initdb
restore from backup

Of course the backup/restore are optional if you don't care about your
data...

On Mon, Jun 20, 2005 at 11:09:36PM -0700, gayatri ganpaa wrote:
> Hi,
> 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?
>  
> Thanks,
> gayatri.
> 
> __________________________________________________
> 
> 
> 		
> ---------------------------------
> Yahoo! Sports
>  Rekindle the Rivalries. Sign up for Fantasy Football

-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: varying pagesize in postgresql
  2005-06-21 06:09 varying pagesize in postgresql gayatri ganpaa <gayatri_ganpaa@yahoo.com>
  2005-06-21 16:58 ` Re: varying pagesize in postgresql Jim C. Nasby <decibel@decibel.org>
@ 2005-06-21 17:22   ` Alvaro Herrera <alvherre@surnet.cl>
  2005-06-22 04:10     ` Re: varying pagesize in postgresql gayatri ganpaa <gayatri_ganpaa@yahoo.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Alvaro Herrera @ 2005-06-21 17:22 UTC (permalink / raw)
  To: Jim C. Nasby <decibel@decibel.org>; +Cc: gayatri ganpaa <gayatri_ganpaa@yahoo.com>; pgsql-admin

[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 (<alvherre[a]surnet.cl>)
"God is real, unless declared as int"



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: varying pagesize in postgresql
  2005-06-21 06:09 varying pagesize in postgresql gayatri ganpaa <gayatri_ganpaa@yahoo.com>
  2005-06-21 16:58 ` Re: varying pagesize in postgresql Jim C. Nasby <decibel@decibel.org>
  2005-06-21 17:22   ` Re: varying pagesize in postgresql Alvaro Herrera <alvherre@surnet.cl>
@ 2005-06-22 04:10     ` gayatri ganpaa <gayatri_ganpaa@yahoo.com>
  0 siblings, 0 replies; 4+ messages in thread

From: gayatri ganpaa @ 2005-06-22 04:10 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@surnet.cl>; pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2005-06-22 04:10 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-21 06:09 varying pagesize in postgresql gayatri ganpaa <gayatri_ganpaa@yahoo.com>
2005-06-21 16:58 ` Jim C. Nasby <decibel@decibel.org>
2005-06-21 17:22   ` Alvaro Herrera <alvherre@surnet.cl>
2005-06-22 04:10     ` gayatri ganpaa <gayatri_ganpaa@yahoo.com>

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