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

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: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

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)
ALTER TABLE gbobjects OWNER TO postgres;

-- Index: nid_object

CREATE INDEX nid_object
  ON gbobjects
  USING btree


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
postgresql configuration files as it is i.e. they are the default
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


pgsql-performance by date

Next:From: Andreas ThielDate: 2009-12-04 23:03:12
Subject: Large DB, several tuning questions: Index sizes, VACUUM, REINDEX, Autovacuum
Previous:From: Scott CareyDate: 2009-12-04 00:04:32
Subject: Re: SSD + RAID

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