Thread: optimiser problem

optimiser problem

From

Gavin Sherry

Date:

15 May 2001, 03:57:07

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

Re: optimiser problem

From

Gavin Sherry

Date:

15 May 2001, 04:30:34

Forgot to add that naturally, I have VACUUM ANALYZE'd.

Gavin

Re: optimiser problem

From

Tom Lane

Date:

15 May 2001, 04:31:30

Gavin Sherry <swm@linuxworld.com.au> writes:
> CREATE TABLE "b" (
>         "id" bigint,
>         "string" text
> );

> SELECT * FROM b WHERE id=1;

Try "WHERE id = 1::bigint".

(Hey Bruce, is there anything about this in the FAQ?)
        regards, tom lane

Re: optimiser problem

From

Stephan Szabo

Date:

15 May 2001, 04:47:38

On Tue, 15 May 2001, Gavin Sherry wrote:

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

Because of a problem with the typing of int constants, you'll need
to explicitly cast your constant into an int8 in order to use the 
index (where id=1::int8).

> So, a select on b as follows:
> 
> SELECT * FROM b WHERE id=1;

Re: optimiser problem

From

Bruce Momjian

Date:

15 May 2001, 13:32:29

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > CREATE TABLE "b" (
> >         "id" bigint,
> >         "string" text
> > );
> 
> > SELECT * FROM b WHERE id=1;
> 
> Try "WHERE id = 1::bigint".
> 
> (Hey Bruce, is there anything about this in the FAQ?)

No, there is something in the TODO but not the FAQ.  Can you give me
some text?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026