query cost too high, anyway to reduce it

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

Responses

Browse pgsql-performance by date

  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