Postgres Pro
Downloads
Home   >   mailing lists

Thread: why is this index not being used?

why is this index not being used?

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

Re: why is this index not being used?

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

Re: why is this index not being used?

From
"Gavin M. Roy"
Date:
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

mike@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 majordomo@postgresql.org
>>
>>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

Re: why is this index not being used?

From
"scott.marlowe"
Date:
On Fri, 5 Mar 2004 mike@linkify.com wrote:

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

Rerun that query with explain analyze to see the difference between what
the planner expected in terms of numbers of rows, and what it really got.

Note that the sequential scan is happening on a table that the planner
thinks it is going to get 113214 rows back.  If the width of that table
really does average only 11, then it is likely you can fit many many
tuples in a single page, and if the number of rows in said table are
anything less than a couple million, it is unlikely that an index scan is
an win, as only selecting 5% of the table looks like it will hit every
page.

->  Seq Scan on document d  (cost=0.00..5629.14 rows=113214 width=11)

You can try "set enable_seqscan = off" and see if the query runs any
faster, by the way.