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