Understanding explain costs

From: David Link <dlink(at)soundscan(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Understanding explain costs
Date: 2001-10-11 20:18:53
Message-ID: 3BC5FEAD.A07FA753@soundscan.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
Trying to understand the planner estimate costs ... one index scan
seems to be much more expensive then another. Here are the facts:

tiger=# \dbk_inv
Table "bk_inv"
Attribute | Type | Modifier
-----------+--------------+----------
store | varchar(5) |
isbn | varchar(10) | not null
qty | numeric(5,0) |
week | numeric(6,0) |
Indices: bk_inv_isbn_idx,
bk_inv_store_idx

tiger=# \dbk_inv_isbn_idx
Index "bk_inv_isbn_idx"
Attribute | Type
-----------+-------------
isbn | varchar(10)
btree

tiger=# \dbk_inv_store_idx
Index "bk_inv_store_idx"
Attribute | Type
-----------+------------
store | varchar(5)
btree

There are about 50,000,000 rows and you can see a full scan on the table
is expensive:

tiger=# explain select * from bk_inv;
NOTICE: QUERY PLAN:

Seq Scan on bk_inv (cost=0.00..999623.77 rows=46790877 width=48)

However using the isbn index costs are much less:

tiger=# explain select * from bk_inv where isbn = 'foo';
NOTICE: QUERY PLAN:

Index Scan using bk_inv_isbn_idx on bk_inv (cost=0.00..53.13
rows=13 width=48)

However, however using the store index costs are still rather high -
why is that!!??

tiger=# explain select * from bk_inv where store = 'foo';
NOTICE: QUERY PLAN:

Index Scan using bk_inv_store_idx on bk_inv (cost=0.00..53456.09
rows=13488 width=48)

Incidently the store index is slightly smaller than the isbn index ...

[postgres(at)roma tiger]$ ls -l bk_inv*
-rw------- 1 postgres postgres 1073741824 Oct 10 14:28 bk_inv
-rw------- 1 postgres postgres 1073741824 Oct 10 10:15 bk_inv.1
-rw------- 1 postgres postgres 1073741824 Oct 10 10:17 bk_inv.2
-rw------- 1 postgres postgres 1073741824 Oct 10 10:19 bk_inv.3
-rw------- 1 postgres postgres 60841984 Oct 11 15:51 bk_inv.4
-rw------- 1 postgres postgres 1073741824 Oct 10 13:37
bk_inv_isbn_idx
-rw------- 1 postgres postgres 566288384 Oct 10 14:31
bk_inv_isbn_idx.1
-rw------- 1 postgres postgres 1073741824 Oct 11 13:13
bk_inv_store_idx
-rw------- 1 postgres postgres 65921024 Oct 11 13:13
bk_inv_store_idx.1

Am I missing certain fundamentals about the planner/executor?

Thanks, David Link

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John Burski 2001-10-11 20:35:54 Postmaster re-start problem
Previous Message Peter Eisentraut 2001-10-11 20:03:47 Re: "Relation x does not exist" error when x does exist