Re: index not used?

-----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-----