From: | nair rajiv <rajivnair(at)gnu(dot)org> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | query cost too high, anyway to reduce it |
Date: | 2009-12-04 10:15:25 |
Message-ID: | d67ff5e60912040215l58f83a19tc096c5b2e251ac98@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
----------------------------------------------
TABLE STRUCTURE
----------------------------------------------
CREATE TABLE gbobjects
(
ssid bigint NOT NULL,
nid character varying NOT NULL,
inid bigint NOT NULL,
uid bigint NOT NULL,
status character varying,
noofchanges integer NOT NULL,
fieldschanged character varying[] NOT NULL,
changetype bigint[] NOT NULL,
noofcommits integer NOT NULL,
noofchangesaftercommit integer NOT NULL,
history bigint[] NOT NULL,
gbtimestamp timestamp with time zone DEFAULT now(),
rendered_nbh text,
nbh text,
CONSTRAINT gbobjects_pkey PRIMARY KEY (ssid)
)
WITH (OIDS=FALSE);
ALTER TABLE gbobjects OWNER TO postgres;
-- Index: nid_object
CREATE INDEX nid_object
ON gbobjects
USING btree
(nid);
-------------------------------------------------------
using EXPLAIN
-------------------------------------------------------
We populated the table with data and used EXPLAIN
dbpedia=# EXPLAIN SELECT nid,max(ssid) FROM gbobjects where ssid<=
100000 group by nid ;
QUERY PLAN
--------------------------------------------------------------------------------------------------
GroupAggregate (cost=20966.03..22944.49 rows=98923 width=27)
-> Sort (cost=20966.03..21213.34 rows=98923 width=27)
Sort Key: nid
-> Index Scan using ssid_object on gbobjects (cost=0.00..10388.88
rows=98923 width=27)
Index Cond: (ssid <= 100000)
Total rows : *875459 *
*The cost is very high. Is there a way to reduce the cost ?. We have kept
the
postgresql configuration files as it is i.e. they are the default
configuration
files.* Can the cost be reduced by changing some parameters in
postgresql.conf file. If yes which are those parameters ?
*Operating system used : ubuntu-9.04
postgresql version : 8.3
Ram : 2 GB
*
Thank you in advance
Rajiv nair
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Thiel | 2009-12-04 23:03:12 | Large DB, several tuning questions: Index sizes, VACUUM, REINDEX, Autovacuum |
Previous Message | Scott Carey | 2009-12-04 00:04:32 | Re: SSD + RAID |