Re: index not used?

Scott Marlowe writes:
> 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.

Bingo.

=> explain select * from table where col='302' and row =100600400;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using idx2 on table (cost=0.00..5.27 rows=1 width=14)
Index Cond: ((col = 302::smallint) AND ("row" = 100600400))
(2 rows)

=> explain select * from table where col=302 and row =100600400;
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on table (cost=100000000.00..102612533.00 rows=1 width=14)
Filter: ((col = 302) AND ("row" = 100600400))
(2 rows)

Wow, that sure is a big difference for such a small "change" in the
query. Thank you very much!