index not used?

#1

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#2

On Wed, 2004-10-20 at 08:06, Dan Pelleg wrote:

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#3

Scott Marlowe writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#4

On Wed, 2004-10-20 at 09:45, Dan Pelleg wrote:

Quote:

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#5

Scott Marlowe writes:

Quote:

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!

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#6

Scott Marlowe wrote:

Quote:

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

#7

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Scott Marlowe wrote:

Quote:

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

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

index not used?

comp.databases.postgresql.general comp.databases.postgresql.general

index not used? - 10-20-2004 , 09:06 AM

Re: index not used? - 10-20-2004 , 10:44 AM

Re: index not used? - 10-20-2004 , 10:45 AM

Re: index not used? - 10-20-2004 , 10:55 AM

Re: index not used? - 10-20-2004 , 11:14 AM

Re: index not used? - 10-22-2004 , 06:11 PM

Re: index not used? - 10-23-2004 , 04:04 AM