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

: Cost calculation for EXPLAIN output

From: Venkat Balaji <venkat(dot)balaji(at)verse(dot)in>
To: PGSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: : Cost calculation for EXPLAIN output
Date: 2012-02-23 12:21:26
Message-ID: CAFrxt0iZrnNUkCjMQOm4JaOz8Y7wG4cd_26nHr2Op5cm9RxTag@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello,

I am trying to understand the analysis behind the "cost" attribute in
EXPLAIN output.

postgres = # explain select * from table_event where seq_id=8520960;


                                        QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Scan using te_pk on table_event  (cost=0.00..13.88  rows=1  width=62)
   Index Cond: (sequence_id = 8520960)

The cost is "13.88" to fetch 1 row by scanning an Primary Key indexed
column.

Isn't the cost for fetching 1 row is too high ?

On the same table, the cost calculation for scanning the full table is
looking justified --

postgres=# explain select * from table_event;

                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on table_event  (cost=0.00..853043.44 rows=38679544 width=62)
(1 row)

(disk pages read * seq_page_cost) + (rows scanned * cpu_tuple_cost) = (466248 *
1) + (38679544 * 0.01) = 853043.44

By the way below are the details -

Version - Postgres-9.0

Table size is            - 3643 MB
+Indexes the size is - 8898 MB

I am looking for a way to reduce cost as much as possible because the query
executes 100000+ times a day.

Any thoughts ?

Thanks,
VB

Responses

pgsql-performance by date

Next:From: Thom BrownDate: 2012-02-23 12:34:29
Subject: Re: set autovacuum=off
Previous:From: Richard HuxtonDate: 2012-02-23 11:23:09
Subject: Re: Very long deletion time on a 200 GB database

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