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?
--Dan
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
