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