On Mon, Aug 06, 2007 at 10:35:50AM +0200, Marc Dirix wrote: > create table test (id int8, info text); > <insert 10,000 rows> > select * from test where id=456; > > will result in a sequential scan. Why? Because the default integer type > is int4, and your id field is int8. Cast the value to int8, and watch it > use an index scan:
That was fixed ages ago...
decibel=# create table i as select * from generate_series(1,10000) i;
\SELECT
decibel=# \d i
Table "public.i"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
decibel=# create index i_i on i(i);
CREATE INDEX
decibel=# explain select * from i where i=828;
QUERY PLAN
-------------------------------------------------------------------
Bitmap Heap Scan on i (cost=2.17..49.87 rows=50 width=4)
Recheck Cond: (i = 828)
-> Bitmap Index Scan on i_i (cost=0.00..2.17 rows=50 width=0)
Index Cond: (i = 828)
(4 rows)
decibel=# select version();
version
-------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.4 on powerpc-apple-darwin8.8.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 20041026 (Apple Computer, Inc.
build 4061)
(1 row)
decibel=#
--
Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
pgpJQwMQ0hBPU.pgp
Description: PGP signature
_______________________________________________ Dbmail-dev mailing list [email protected] http://twister.fastxs.net/mailman/listinfo/dbmail-dev
