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: d67ff5e60912040215l58f83a19tc096c5b2e251ac98@mail.gmail.com (view raw or flat)
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

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-2014 The PostgreSQL Global Development Group