Обсуждение: - Slow Query

HI Gurus ,

i have this query (i think is a simple one)
it takes me 1,7s to run it, it's not to long, but considering it takes 1,7s to return 71lines makes me wonder... is there anyother way to do this, on a first look??

any sugestion would be largely appreciated.

SELECT distinct on (bien.uid) bien.uid , bien.date_creation , bien.date_modification , bien.nom ,  bien.numero_voie , bien.mer , bien.proximite ,  bien.nom_voie , bien.type_voie , bien.lieudit ,  bien.arrondissement , bien.montagne , bien.complement_adresse , bien.xy_geo , bien.ref_type_avancement ,   bien.ref_agence , bien.acces_handicape , bien.surface_totale , bien.ref_type_transaction ,  bien.reference_bien ,
                    bien.ref_type_bien ,  bien.bien_exception , bien.video_online , bien.geom , habitation.nombre_de_chambres, habitation.nombre_de_wc ,
                    prix.montant , ville.nom ,ville.abreviation , ville.code_insee , ville.code_postal ,
                    freguesia_ville.code_insee , freguesia_ville.code_postal , freguesia_ville.ref_freguesia , freguesia_ville.ref_ville ,
                    freguesia.nom , freguesia.numero , departement.nom , departement.numero , region.nom ,region.numero , zone.zone_public , type_transaction.nom, mandat.numero_mandat_pt
FROM bien
LEFT outer JOIN prix ON prix.ref_bien = bien.uid  AND prix.ref_type_prix in (2,9) and prix.montant !=0  LEFT outer JOIN habitation on habitation.uid = bien.uid
LEFT outer JOIN ville ON ville.uid = bien.ref_ville LEFT outer JOIN freguesia_ville ON freguesia_ville.ref_ville =ville.uid
LEFT outer JOIN freguesia ON freguesia.uid = freguesia_ville.ref_freguesia
LEFT outer JOIN departement ON departement.uid =ville.ref_departement LEFT outer JOIN region ON region.uid = departement.ref_region
LEFT outer JOIN zone ON zone.ref_bien = bien.uid JOIN imagebien ON imagebien.ref_bien = bien.uid left outer join mandat on mandat.ref_bien=bien.uid
LEFT outer JOIN type_transaction ON type_transaction.uid = bien.ref_type_transaction
LEFT OUTER JOIN agence on agence.uid = bien.ref_agence
WHERE imagebien.uid IS NOT NULL AND bien.statut = 0 and bien.visible_internet = 1 and bien.ref_agence = XXXXXXX




thanks.

RC

Hi Rui,
> i have this query (i think is a simple one)

Could you EXPLAIN ANALYZE the query and show the results please?

Thanks,
Mike

Rui Carvalho wrote:
> SELECT distinct on (bien.uid) bien.uid , bien.date_creation ,
> bien.date_modification , bien.nom ,  bien.numero_voie , bien.mer ,
> bien.proximite ,  bien.nom_voie , bien.type_voie , bien.lieudit ,
> bien.arrondissement , bien.montagne , bien.complement_adresse ,
> bien.xy_geo , bien.ref_type_avancement ,   bien.ref_agence ,
> bien.acces_handicape , bien.surface_totale , bien.ref_type_transaction
> ,  bien.reference_bien ,
>                     bien.ref_type_bien ,  bien.bien_exception ,
> bien.video_online , bien.geom , habitation.nombre_de_chambres,
> habitation.nombre_de_wc ,
>                     prix.montant , ville.nom ,ville.abreviation ,
> ville.code_insee , ville.code_postal ,
>                     freguesia_ville.code_insee ,
> freguesia_ville.code_postal , freguesia_ville.ref_freguesia ,
> freguesia_ville.ref_ville ,
>                     freguesia.nom , freguesia.numero , departement.nom
> , departement.numero , region.nom ,region.numero , zone.zone_public ,
> type_transaction.nom, mandat.numero_mandat_pt
> FROM bien
> LEFT outer JOIN prix ON prix.ref_bien = bien.uid  AND
> prix.ref_type_prix in (2,9) and prix.montant !=0  LEFT outer JOIN
> habitation on habitation.uid = bien.uid
> LEFT outer JOIN ville ON ville.uid = bien.ref_ville LEFT outer JOIN
> freguesia_ville ON freguesia_ville.ref_ville =ville.uid
> LEFT outer JOIN freguesia ON freguesia.uid = freguesia_ville.ref_freguesia
> LEFT outer JOIN departement ON departement.uid =ville.ref_departement
> LEFT outer JOIN region ON region.uid = departement.ref_region
> LEFT outer JOIN zone ON zone.ref_bien = bien.uid JOIN imagebien ON
> imagebien.ref_bien = bien.uid left outer join mandat on
> mandat.ref_bien=bien.uid
> LEFT outer JOIN type_transaction ON type_transaction.uid =
> bien.ref_type_transaction
> LEFT OUTER JOIN agence on agence.uid = bien.ref_agence
> WHERE imagebien.uid IS NOT NULL AND bien.statut = 0 and
> bien.visible_internet = 1 and bien.ref_agence = XXXXXXX
>

You need to run explain analyze on the query, and post the results
This will tell us where the time is getting eaten up and other problems
that might be in the query.
Also need to know the version of Postgresql???

 >  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

hum thanks a lot for the quick answer,

if is not abuse of your patience

what is the best alternative to the LEFT OUTER JOINS?


RC

On Wed, Jul 1, 2009 at 11:37 AM, Rui Carvalho<rui.hmcarvalho@gmail.com> wrote:
> hum thanks a lot for the quick answer,
>
> if is not abuse of your patience
>
> what is the best alternative to the LEFT OUTER JOINS?

Hard to say.  Generally, when you really do need a left, right, or
full outer join, you need it, and there's not a lot of alternatives.
Sometimes putting a where clause portion into the on clause helps.
like:

select * from a left join b on (a.id=b.id) where a.somefield=2

might run faster with

select * from a left join b on (a.id=bid. and a.somefield=2);

but it's hard to say.  I'd definitely post it to the list and see who
knows what.

Scott Marlowe <scott.marlowe@gmail.com> writes:
> Sometimes putting a where clause portion into the on clause helps.
> like:
> select * from a left join b on (a.id=b.id) where a.somefield=2
> might run faster with
> select * from a left join b on (a.id=bid. and a.somefield=2);
> but it's hard to say.

Uh, those are not the same query ... they will give different results
for rows with a.somefield different from 2.

            regards, tom lane

Rui Carvalho wrote:
> hum thanks a lot for the quick answer,
>
> if is not abuse of your patience
>
> what is the best alternative to the LEFT OUTER JOINS?
I meant I wasn't sure whether you really meant *outer* joins. Too many
of them looked kinda suspicious :-)

If you *do* need them, then there is no alternative, as Scott said.

Mike

On Wed, Jul 1, 2009 at 11:52 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
> Scott Marlowe <scott.marlowe@gmail.com> writes:
>> Sometimes putting a where clause portion into the on clause helps.
>> like:
>> select * from a left join b on (a.id=b.id) where a.somefield=2
>> might run faster with
>> select * from a left join b on (a.id=bid. and a.somefield=2);
>> but it's hard to say.
>
> Uh, those are not the same query ... they will give different results
> for rows with a.somefield different from 2.

How so?  Neither should return any rows with a.somefield <> 2.  Or are
you talking where a.somefield is null?

Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Wed, Jul 1, 2009 at 11:52 AM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>> Scott Marlowe <scott.marlowe@gmail.com> writes:
>>> Sometimes putting a where clause portion into the on clause helps.
>>> like:
>>> select * from a left join b on (a.id=b.id) where a.somefield=2
>>> might run faster with
>>> select * from a left join b on (a.id=bid. and a.somefield=2);
>>> but it's hard to say.
>>
>> Uh, those are not the same query ... they will give different results
>> for rows with a.somefield different from 2.

> How so?  Neither should return any rows with a.somefield <> 2.

Wrong.  The second will return rows with somefield <> 2, null-extended
(whether or not there is any match on id).

            regards, tom lane

Why so? Is not changing "freguesia_ville.ref_ville =ville.uid" to "freguesia_ville.ref_ville =bien.uid" enough (to prevent cases when ville.uid is null as result of join)?

Sorry, it was an error in previous letter.