Re: Trying to get postgres to use an index

<mike.wertheim@linkify.com> writes:
> Here is the corrected version, which still has the sequential scan...

> explain  select notificationID from NOTIFICATION n, ITEM i where n.itemID
> = i.itemID and i.projectID = 12;
>                                             QUERY PLAN
> ---------------------------------------------------------------------------
> -----------------------
>  Hash Join  (cost=2237.54..15382.32 rows=271 width=44)
>    Hash Cond: ("outer".itemid = "inner".itemid)
>    ->  Seq Scan on notification n  (cost=0.00..12023.71 rows=223671
> width=48)
>    ->  Hash  (cost=2235.31..2235.31 rows=895 width=4)
>          ->  Index Scan using item_ix_item_4_idx on item i
>          (cost=0.00..2235.31 rows=895width=4)
>                Index Cond: (projectid = 12)

This seems like a perfectly fine plan to me.  If it were turned around
into a nested indexscan as you suggest, there would need to be 895
indexscans of NOTIFICATION (one for each row retrieved from ITEM)
and from your original mail we can see the planner thinks that an
indexscan on NOTIFICATION will take about 129 cost units, for a total
cost of 129 * 895 = 115455 units (and that's not counting the indexscan
on ITEM nor any join overhead).  So at least according to these
estimates, using the index would take 10x more time than this plan.

If you want to see whether this costing is accurate, you could do
EXPLAIN ANALYZE for this way and the other (I expect that you'd get the
other if you did "set enable_seqscan = off").  But with a 10x
discrepancy I suspect the planner probably did the right thing.

            regards, tom lane