Re: Unexpected query plan

Dave Cramer writes:

> test=# create table custbase (phonenumber int8, svctype char, svcchoice
> char, billtype char, ctype char);
> CREATE
> test=# create index custbaseidx on custbase (phonenumber);
> CREATE
> test=# \d custbase
>            Table "custbase"
>   Attribute  |     Type     | Modifier
> -------------+--------------+----------
>  phonenumber | bigint       |
>  svctype     | character(1) |
>  svcchoice   | character(1) |
>  billtype    | character(1) |
>  ctype       | character(1) |
> Index: custbaseidx
>
> test=# explain select * from custbase where phonenumber=5199400858;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on custbase  (cost=0.00..25.00 rows=10 width=56)
>
> EXPLAIN
> test=# explain select * from custbase where phonenumber='5199400858';
> NOTICE:  QUERY PLAN:
>
> Index Scan using custbaseidx on custbase  (cost=0.00..8.14 rows=10 width=56)

This is a long-standing problem, and you picked the right workaround.
Btw., I would think that bigint is not the optimal choice for phone
numbers.  I would use a character type.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter