Re: index not used?

On Wed, 2004-10-20 at 09:45, Dan Pelleg wrote:
> Scott Marlowe writes:
>  > On Wed, 2004-10-20 at 08:06, Dan Pelleg wrote:
>  > > I'm trying to access a table with about 120M rows. It's a vertical version
>  > > of a table with 360 or so columns. The new columns are: original item col,
>  > > original item row, and the value.
>  > >
>  > > I created an index:
>  > >
>  > > CREATE INDEX idx on table (col, row)
>  > >
>  > > however, selects are still very slow. It seems it still needs a sequential
>  > > scan:
>  > >
>  > > EXPLAIN SELECT * FROM table WHERE col=1 AND row=10;
>  > >                                   QUERY PLAN
>  > > ------------------------------------------------------------------------------
>  > >  Seq Scan on table  (cost=100000000.00..102612533.00 rows=1 width=14)
>  > >    Filter: ((col = 1) AND ("row" = 10))
>  > >
>  > > What am I doing wrong?
>  >
>  > What type are row and col?  If they're bigint (i.e. not int / int4) then
>  > you might need to quote the value to get the query to use an index:
>  >
>  > SELECT * FROM table WHERE col='1' AND row='10';
>  >
>  > also, have you vacuumed / analyzed the table?  I'm assuming yes.
>
> They're not bigints:
>
> CREATE TABLE table (col int2, row integer, val double precision)
>
> Yes, I vacuumed and analyzed, right after creating the index. Should I try
> and issue a few queries beforehand?

but one is an int2 (i.e. not int / int4) so you'll need to quote that
value to get an index to work.  Note this is fixed in 8.0 I understand.