| From: | Mike Ivanov <mikei(at)activestate(dot)com> |
|---|---|
| To: | Rui Carvalho <rui(dot)hmcarvalho(at)gmail(dot)com> |
| Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: - Slow Query |
| Date: | 2009-07-01 17:12:23 |
| Message-ID: | 4A4B98F7.2070202@activestate.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
> Merge Join (cost=111885.70..319492.88 rows=13016048 width=620)
The outermost merge join has to go through 13 million rows. If you
remove "distinct on (bien.uid)", you'll see that.
> LEFT outer JOIN ville ON ville.uid = bien.ref_ville
> LEFT outer JOIN freguesia_ville ON freguesia_ville.ref_ville =ville.uid
This is not enough. You have to add this condition as well:
AND bien.ref_ville = freguesia_ville.ref_ville
In other words, when you link three tables by a common field, all three
relationships should be explicitly expressed, otherwise you'll have this
type of explosive row multiplication.
Although I don't quite understand the purpose of the query, I don't
think you need all those OUTER joins.
Regards,
Mike
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Rui Carvalho | 2009-07-01 17:37:30 | Re: - Slow Query |
| Previous Message | justin | 2009-07-01 16:41:15 | Re: - Slow Query |