why is this index not being used?

[mike]

Hey all,

I'm using PostgreSQL 7.3.4.

I have a query that isn't using a particular index, and I'm wondering why.

The query is:
select i.ItemID, d.Extension from ITEM i, SHARING s, DOCUMENT d where
i.ItemID = d.ItemID AND s.ItemID = i.DomainID AND s.UserIDOfSharee = 12

Item's primary key is ItemID.
Document's primary key is ItemID.
Sharing's primary key is (ItemID, UserIDOfSharee).
Item has index item_ix_item_3_idx on (DomainID, ItemID).
Sharing has index sharing_ix_sharing_1_idx on (UserIDOfSharee, ItemID).

Explain says:
Hash Join (cost=25526.26..31797.78 rows=6105 width=23)
Hash Cond: ("outer".itemid = "inner".itemid)
-> Seq Scan on document d (cost=0.00..5629.14 rows=113214 width=11)
-> Hash (cost=25502.60..25502.60 rows=9465 width=12)
-> Merge Join (cost=310.16..25502.60 rows=9465 width=12)
Merge Cond: ("outer".domainid = "inner".itemid)
-> Index Scan using item_ix_item_3_idx on item i
(cost=0.00..24634.71 rows=175519 width=8)
-> Sort (cost=310.16..310.47 rows=123 width=4)
Sort Key: s.itemid
-> Index Scan using sharing_ix_sharing_1_idx on
sharing s (cost=0.00..305.88 rows=123 width=4)
Index Cond: (useridofsharee = 12)
Why is there a Seq Scan on Document? How can I get it to use Document's
primary key?
Thanks!
Mike


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[mike]

BTW -- We vacuum nightly, and running vacuum analyze doesn't make a
difference.

Hey all,

I'm using PostgreSQL 7.3.4.

I have a query that isn't using a particular index, and I'm wondering
why.

The query is:
select i.ItemID, d.Extension from ITEM i, SHARING s, DOCUMENT d where
i.ItemID = d.ItemID AND s.ItemID = i.DomainID AND s.UserIDOfSharee = 12

Item's primary key is ItemID.
Document's primary key is ItemID.
Sharing's primary key is (ItemID, UserIDOfSharee).
Item has index item_ix_item_3_idx on (DomainID, ItemID).
Sharing has index sharing_ix_sharing_1_idx on (UserIDOfSharee, ItemID).

Explain says:
Hash Join (cost=25526.26..31797.78 rows=6105 width=23)
Hash Cond: ("outer".itemid = "inner".itemid)
-> Seq Scan on document d (cost=0.00..5629.14 rows=113214 width=11)
-> Hash (cost=25502.60..25502.60 rows=9465 width=12)
-> Merge Join (cost=310.16..25502.60 rows=9465 width=12)
Merge Cond: ("outer".domainid = "inner".itemid)
-> Index Scan using item_ix_item_3_idx on item i
(cost=0.00..24634.71 rows=175519 width=8)
-> Sort (cost=310.16..310.47 rows=123 width=4)
Sort Key: s.itemid
-> Index Scan using sharing_ix_sharing_1_idx on
sharing s (cost=0.00..305.88 rows=123 width=4)
Index Cond: (useridofsharee = 12)
Why is there a Seq Scan on Document? How can I get it to use
Document's primary key?
Thanks!
Mike


---------------------------(end of
broadcast)--------------------------- TIP 1: subscribe and unsubscribe
commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Gavin M. Roy]

You can always try and force it by doing

"set enable_seqscan=0"

I'd try explain analyze on the query with it on (=1) and off (=0) and
see why the planner likes seqscan better.

Gavin

mi**@linkify.com wrote:

BTW -- We vacuum nightly, and running vacuum analyze doesn't make a
difference.

Hey all,

I'm using PostgreSQL 7.3.4.

I have a query that isn't using a particular index, and I'm wondering
why.

The query is:
select i.ItemID, d.Extension from ITEM i, SHARING s, DOCUMENT d where
i.ItemID = d.ItemID AND s.ItemID = i.DomainID AND s.UserIDOfSharee = 12

Item's primary key is ItemID.
Document's primary key is ItemID.
Sharing's primary key is (ItemID, UserIDOfSharee).
Item has index item_ix_item_3_idx on (DomainID, ItemID).
Sharing has index sharing_ix_sharing_1_idx on (UserIDOfSharee, ItemID).

Explain says:
Hash Join (cost=25526.26..31797.78 rows=6105 width=23)
Hash Cond: ("outer".itemid = "inner".itemid)
-> Seq Scan on document d (cost=0.00..5629.14 rows=113214 width=11)
-> Hash (cost=25502.60..25502.60 rows=9465 width=12)
-> Merge Join (cost=310.16..25502.60 rows=9465 width=12)
Merge Cond: ("outer".domainid = "inner".itemid)
-> Index Scan using item_ix_item_3_idx on item i
(cost=0.00..24634.71 rows=175519 width=8)
-> Sort (cost=310.16..310.47 rows=123 width=4)
Sort Key: s.itemid
-> Index Scan using sharing_ix_sharing_1_idx on
sharing s (cost=0.00..305.88 rows=123 width=4)
Index Cond: (useridofsharee = 12)
Why is there a Seq Scan on Document? How can I get it to use
Document's primary key?
Thanks!
Mike


---------------------------(end of
broadcast)--------------------------- TIP 1: subscribe and unsubscribe
commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster