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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2012-02-23 12:34:29 | Re: set autovacuum=off |
Previous Message | Richard Huxton | 2012-02-23 11:23:09 | Re: Very long deletion time on a 200 GB database |