Re: index not used?
От | Scott Marlowe |
---|---|
Тема | Re: index not used? |
Дата | |
Msg-id | 1098287727.21035.56.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: index not used? (Dan Pelleg <daniel+pgsql@pelleg.org>) |
Ответы |
Re: index not used?
|
Список | 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.
В списке pgsql-general по дате отправления: