Re: Postgres scalability and performance on windows

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
________________________________________________________________________