Re: why is this index not being used?

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 majordomo@postgresql.org