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

Re: Postgres scalability and performance on windows

From: "Gopal" <gopal(at)getmapping(dot)com>
To: "Frank Wiles" <frank(at)wiles(dot)org>,"Guido Neitzer" <lists(at)event-s(dot)net>,"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>,<postgis-users(at)postgis(dot)refractions(dot)net>
Subject: Re: Postgres scalability and performance on windows
Date: 2006-11-28 12:22:31
Message-ID: A5DE6132B8D812419321747E42710E2204C080@EXCHANGE01.gm.local (view raw or flat)
Thread:
Lists: pgsql-performance
Tom,

This is the query and the schema....

Query is :
SELECT  subq.percentCover, ds.datasetname, ds.maxresolution
                FROM 
                ( 
                        select
sum(area(intersection(snaptogrid(chunkgeometry,0.00000001), 
                        GeometryFromText('POLYGON((-0.140030845589332
50.8208343077265,-0.138958398039148 50.8478005422809,-0.0963639712296823
50.8471133071392,-0.0974609286275892 50.8201477285483,-0.140030845589332
50.8208343077265))',4326))) * 100/ (0.00114901195862628)) as
percentCover, 
                        datasetid as did from 
                        tbl_metadata_chunks 
                        where chunkgeometry &&
GeometryFromText('POLYGON((-0.140030845589332
50.8208343077265,-0.138958398039148 50.8478005422809,-0.0963639712296823
50.8471133071392,-0.0974609286275892 50.8201477285483,-0.140030845589332
50.8208343077265))',4326)
                        and datasetid in (select datasetid from
tbl_metadata_dataset where typeofdataid=1)
                        group by did
                        order by did desc                       
                )
                AS subq INNER JOIN tbl_metadata_dataset AS 
                ds ON subq.did = ds.datasetid         
                ORDER by ceil(subq.percentCover),1/ds.maxresolution
DESC;


Schema is

Table 1
CREATE TABLE public.tbl_metadata_dataset
(
datasetname varchar(70) NOT NULL,
maxresolution real,
typeofdataid integer NOT NULL,
datasetid serial NOT NULL,
CONSTRAINT "PK_Dataset" PRIMARY KEY (datasetid)
);
-- Indexes
CREATE INDEX dsnameindex ON tbl_metadata_dataset USING btree
(datasetname);-- Owner
ALTER TABLE public.tbl_metadata_dataset OWNER TO postgres;
-- Triggers
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2196039" AFTER DELETE ON
tbl_metadata_dataset FROM tbl_metadata_chunks NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del"('dsid',
'tbl_metadata_chunks', 'tbl_metadata_dataset', 'UNSPECIFIED',
'datasetid', 'datasetid');
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2196040" AFTER UPDATE ON
tbl_metadata_dataset FROM tbl_metadata_chunks NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd"('dsid',
'tbl_metadata_chunks', 'tbl_metadata_dataset', 'UNSPECIFIED',
'datasetid', 'datasetid');


Table 2

CREATE TABLE public.tbl_metadata_chunks
(
chunkid serial NOT NULL,
chunkgeometry geometry NOT NULL,
datasetid integer NOT NULL,
CONSTRAINT tbl_metadata_chunks_pkey PRIMARY KEY (chunkid),
CONSTRAINT dsid FOREIGN KEY (datasetid) REFERENCES
tbl_metadata_dataset(datasetid)
);
-- Indexes
CREATE INDEX idx_dsid ON tbl_metadata_chunks USING btree (datasetid);
CREATE UNIQUE INDEX tbl_metadata_chunks_idx2 ON tbl_metadata_chunks
USING btree (nativetlx, nativetly, datasetid);
CREATE INDEX tbl_metadata_chunks_idx3 ON tbl_metadata_chunks USING gist
(chunkgeometry);-- Owner
ALTER TABLE public.tbl_metadata_chunks OWNER TO postgres;
-- Triggers
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2194515" AFTER DELETE ON
tbl_metadata_chunks FROM tbl_metadata_chunkinfo NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_restrict_del"('fk',
'tbl_metadata_chunkinfo', 'tbl_metadata_chunks', 'UNSPECIFIED',
'chunkid', 'chunkid');
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2194516" AFTER UPDATE ON
tbl_metadata_chunks FROM tbl_metadata_chunkinfo NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_restrict_upd"('fk',
'tbl_metadata_chunkinfo', 'tbl_metadata_chunks', 'UNSPECIFIED',
'chunkid', 'chunkid');
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2196037" AFTER INSERT ON
tbl_metadata_chunks FROM tbl_metadata_dataset NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('dsid',
'tbl_metadata_chunks', 'tbl_metadata_dataset', 'UNSPECIFIED',
'datasetid', 'datasetid');
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_2196038" AFTER UPDATE ON
tbl_metadata_chunks FROM tbl_metadata_dataset NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_upd"('dsid',
'tbl_metadata_chunks', 'tbl_metadata_dataset', 'UNSPECIFIED',
'datasetid', 'datasetid');



-----Original Message-----
From: Frank Wiles [mailto:frank(at)wiles(dot)org] 
Sent: 24 November 2006 17:05
To: Guido Neitzer
Cc: Gopal; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Postgres scalability and performance on windows

On Fri, 24 Nov 2006 09:22:45 +0100
Guido Neitzer <lists(at)event-s(dot)net> wrote:

> > effective_cache_size = 82728                  # typically 8KB each
> Hmm. I don't know what the real effect of this might be as the doc  
> states:
> 
> "This parameter has no effect on the size of shared memory allocated  
> by PostgreSQL, nor does it reserve kernel disk cache; it is used
> only for estimation purposes."

   This is a hint to the optimizer about how much of the database may
   be in the OS level cache.  

 ---------------------------------
   Frank Wiles <frank(at)wiles(dot)org>
   http://www.wiles.org
 ---------------------------------


________________________________________________________________________
This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-11-28 16:24:42
Subject: Re: Postgres scalability and performance on windows
Previous:From: Bill MoranDate: 2006-11-28 11:34:39
Subject: Re: BUG #2784: Performance serious degrades over a period

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