Re: simple join query runs very slowly

On Thu, 25 Mar 2004, Roger Dahl wrote:

> Hello everyone,
>
> I'm having trouble with a slow query and I wonder if anyone can help
> me speed it up. I have read the PostgreSQL documentation and FAQ but
> have been unable to find anything to help me out.
>
> I have three tables: items, boxes and item_box_maps. The items table
> holds ids and item names. The boxes table holds ids and box names. The
> item_box_maps table holds box and item ids that "map" items to boxes.
>
> To register a box as containing a specific item, I just add a record
> in item_box_maps with the appropriate box name id and item name id. I
> have used this approach on SQL Server many times and have always
> gotten excellent performance.
>
> To find out which boxes contain a specific item, I would write a query
> like this:
>
> select b.name,
> from boxes b
> inner join item_box_maps m on m.box_id = b.id
> inner join items i on m.id = m.item_id
 -- is there a typo here?  I'd think it should be i.id=m.item_id
> where i.name = 'hammer';

Hmm, you didn't give a version.  On some versions the above is going
to force the boxes to item_box_maps join to be first (where it looks like
perhaps the item_box_maps to items join first would be better).  You could
try something like:

select b.name from boxes b inner join (item_box_maps m inner join items i
on i.id=m.item_id) on m.box_id=b.id where i.name='hammer'

And see what that gives you plan wise...