From: | "Scott Marlowe" <smarlowe(at)qwest(dot)net> |
---|---|
To: | "Dan Pelleg" <daniel+pgsql(at)pelleg(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: index not used? |
Date: | 2004-10-20 15:55:27 |
Message-ID: | 1098287727.21035.56.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2004-10-20 15:57:42 | Re: Sequence question |
Previous Message | Scott Marlowe | 2004-10-20 15:52:25 | Re: how much ram do i give postgres? |