optimiser problem

From: Gavin Sherry <swm(at)linuxworld(dot)com(dot)au>
To: pgsql-hackers(at)postgresql(dot)org
Subject: optimiser problem
Date: 2001-05-15 04:58:46
Message-ID: Pine.LNX.4.21.0105151449140.10801-100000@linuxworld.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi all,

Can't for the life of me figure out the problem here:

CREATE TABLE "b" (
"id" bigint,
"string" text
);

CREATE INDEX "b_pkey" on "b" using btree ( "id" "int8_ops" );

Given 2000 tuples in b, vacuum verbose analyze:

test=# vacuum verbose analyze b;
NOTICE: --Relation b--
NOTICE: Pages 13: Changed 0, reaped 0, Empty 0, New 0; Tup 2002: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 48, MaxLen
48; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.01u sec.
NOTICE: Index b_pkey: Pages 12; Tuples 2002. CPU 0.00s/0.03u sec.
NOTICE: --Relation pg_toast_2140890--
NOTICE: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen
0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU
0.00s/0.00u sec.
NOTICE: Index pg_toast_2140890_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u
sec.
NOTICE: Analyzing...
VACUUM

So, a select on b as follows:

SELECT * FROM b WHERE id=1;

should not have an EXPLAIN like this:

test=# explain verbose select * from b where id=1;
NOTICE: QUERY DUMP:

{ SEQSCAN :startup_cost 0.00 :total_cost 38.02 :rows 2 :width 20
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 20
:restypmod -1 :resname id :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 20 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 1}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 :restypmod -1 :resname
string
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 2 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 2}}) :qpqual ({ EXPR :typeOid 16 :opType op :oper { OPER :opno
416 :opid 474 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1
:vartype 20 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} {
CONST :consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 1 0 0 0 ] })}) :lefttree <> :righttree <> :extprm
() :locprm () :initplan <> :nprm 0 :scanrelid 1 }
NOTICE: QUERY PLAN:

Seq Scan on b (cost=0.00..38.02 rows=2 width=20)

version is 7.1.

Thanks

Gavin

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gavin Sherry 2001-05-15 05:06:36 Re: optimiser problem
Previous Message Vadim Mikheev 2001-05-15 04:57:12 Re: Postgres bug (working with iserverd)