> explain select notificationID from NOTIFICATION n, ITEM i where
> n.itemID = i.itemID;
> QUERY PLAN
>
> ------------------------------------------------------------------------
> ------
> Hash Join (cost=47162.85..76291.32 rows=223672 width=44)
> Hash Cond: ("outer".itemid = "inner".itemid)
> -> Seq Scan on notification n (cost=0.00..12023.71 rows=223671
> width=48)
> -> Hash (cost=42415.28..42415.28 rows=741028 width=4)
> -> Seq Scan on item i (cost=0.00..42415.28 rows=741028
> width=4)
>
> This query takes about 20 seconds to run.
Well, you're joining the entire two tables, so yes, the seq scan might be
faster.
Try your query with enable_seqscan=0 so it'll use an index scan and
compare the times.
You may be surprised to find that the planner has indeed made the right
choice.
This query selects 223672 rows, are you surprised it's slow ?
What are you trying to do with this query ? Is it executed often ?
If you want to select only a subset of this, use an additional where
condition and the planner will use the index.
