Skip site navigation (1) Skip section navigation (2)

Re: simple join query runs very slowly

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: rdnews(at)dahlsys(dot)com (Roger Dahl)
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: simple join query runs very slowly
Date: 2004-03-26 19:37:13
Message-ID: 26439.1080329833@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
rdnews(at)dahlsys(dot)com (Roger Dahl) writes:
> 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
> where i.name = 'hammer';

You didn't say which PG version you are running, but I bet it is one
that strictly follows the JOIN syntax order.  Try flipping the order of
the JOIN clauses:

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

There is more about this in the Performance Tips section of the docs.

			regards, tom lane

In response to

pgsql-general by date

Next:From: Eric RidgeDate: 2004-03-26 20:08:29
Subject: pg 7.3, cursors, and prepared queries
Previous:From: Ron St-PierreDate: 2004-03-26 19:15:30
Subject: Re: [NOVICE] Simple list tables question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group