Обсуждение: query cost too high, anyway to reduce it

----------------------------------------------
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  ;
                              

On Fri, Dec 4, 2009 at 3:15 AM, nair rajiv <rajivnair@gnu.org> wrote:

> 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.

Compared to what?

> 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 ?

Sure you can change the numbers for random_page_cost and
sequential_page_cost, but the query isn't gonna run faster.  You're
retrieving 875k rows, that's never gonna be cheap.

Better is to run explain analyze and look at the times you're getting
for each step in the query plan.