From: | Gaetano Mendola <mendola(at)bigfoot(dot)com> |
---|---|
To: | Scott Marlowe <smarlowe(at)qwest(dot)net> |
Subject: | Re: index not used? |
Date: | 2004-10-22 23:11:38 |
Message-ID: | 417993AA.6050605@bigfoot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Scott Marlowe wrote:
> 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.
I assume not, seen that cost...
Regards
Gaetano Mendola
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-10-22 23:11:52 | Re: ia64 hostname lookup problem |
Previous Message | Gaetano Mendola | 2004-10-22 23:09:04 | Re: correct example of a functional index usage? |