Re: index not used?

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.