PostgreSQL and explain

From: "Johnson, Shaunn" <SJohnson6(at)bcbsm(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: PostgreSQL and explain
Date: 2002-04-01 16:21:03
Message-ID: 73309C2FDD95D11192E60008C7B1D5BB0452E174@snt452.corp.bcbsm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Howdy:

Running Postgres 7.1.3 on RedHat 7.2 kernel 2.4.7 rel. 10.

I have a question regarding explain and the cost definition.

I have a table I have just loaded:

[snip]

bcn=> select count (*) from db2_class01 ;
count
--------
217490
(1 row)

[/snip]

Then, I run something like this just for kicks - I'm
trying to figure out performance measures ...

[script]

explain
select
c_claim_num,
c_diag_1,
c_mbr_num,
c_mbr_sex
from
db2_class01
where
c_claim_num like '%307A0%'
and
c_contract_num like '%67853%'
and
d_mbr_birth_dt between '1950-01-01' and '1965-06-30'
;
[/script]

The result is this:

[results]

psql:./explain1.sql:15: NOTICE: QUERY PLAN:

Seq Scan on db2_class01 (cost=0.00..30.00 rows=1 width=48)

EXPLAIN

[/results]

NOW - I run vacuum on the same table:

[vacuum]

bcn=> vacuum verbose db2_class01;
NOTICE: --Relation db2_class01--
NOTICE: Pages 3021: Changed 0, reaped 0, Empty 0, New 0; Tup 217490: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 108, MaxLen 108; Re-using:
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.16s/0.04u sec.
VACUUM

[/vacuum]

When I re-run the example script show above, the output is different (which
it should be)
but I don't understand what exactly what IS cost and how do I figure out if
the
cost of doing the query is / should be greater or more efficient.

[after vacuum result]

psql:./explain1.sql:15: NOTICE: QUERY PLAN:

Seq Scan on db2_class01 (cost=0.00..7370.80 rows=1 width=48)

EXPLAIN

[/after vacuum result]

I mean: the difference for cost = 0.00..30.00 and cost = 0.00..7370.80 ...
(forgive my newbie-ness) but this looks like an increase ... why is that?

Thanks!

-X

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-04-01 16:21:54 Re: NOT IN queries
Previous Message Doug McNaught 2002-04-01 16:14:52 Re: NOT IN queries