Postgres Pro
Downloads
Home   >   mailing lists

Thread: index not used?

index not used?

From
Dan Pelleg
Date:
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?

--
 Dan Pelleg

Re: index not used?

From
"Scott Marlowe"
Date:
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.

Re: index not used?

From
Dan Pelleg
Date:
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

Re: index not used?

From
"Scott Marlowe"
Date:
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.

Re: index not used?

From
Dan Pelleg
Date:
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!

Re: index not used?

From
Gaetano Mendola
Date:
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

Re: index not used?

From
Gaetano Mendola
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Scott Marlowe wrote:
| On Fri, 2004-10-22 at 17:11, Gaetano Mendola wrote:
|
|>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...
|>
|
|
| Actually, that cost would likely be caused by set enable_seqscan = off
| wouldn't it?

That's true. This is the second time in these last days that I see someone "tune"
postgres setting enable_seqscan = off.


G.





-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBeh6H7UpzwH2SGd4RAvEDAKDdBI6g484jxv4dzdMwXSRwQpJUhgCfU2W7
4hghwH7rJhsC8mRk+Uo/OsU=
=WCBg
-----END PGP SIGNATURE-----