Re: Problem with JOINS

On Fri, 21 May 2004, Charlie Clark wrote:

> SELECT
> gender.value as anrede_value,
> person.name as person_name,
> person.vorname as person_vorname,
> person.zusatz as person_zusatz,
> person.birthdate as person_birthdate,
> address.strasse as address_strasse,
> address.hausnummer as address_hausnummer,
> address.tel as address_tel,
> address.tel_vor as address_tel_vor,
> address.fax as address_fax,
> address.fax_vor as address_fax_vor,
> address.mobil as address_mobil,
> address.mobil_vor as address_mobil_vor,
> address.plz as address_plz,
> address.ort as address_ort,
> address.e_mail as address_e_mail,
> address.www as address_homepage,
> address.wheelchair as address_wheelchair,
> therapist.id_person
> ,users.roles as service
> FROM person
> INNER JOIN therapist on
> (person.id_person = therapist.id_person)
> INNER JOIN address on
> (person.id_person = address.id_person)
> INNER JOIN gender_list as gender on
> (person.id_gender = gender.id)
> INNER JOIN users on
> (users.id_person = person.id_person)
> INNER JOIN bill ON
> (bill.id_person = person.id_person)
> INNER JOIN bill_status_list AS bs ON
> (bs.id = bill.id_status)
> WHERE true
> AND
> person.id_status = 2
> AND
> person.id_authorise = 2
> AND
> ltrim(lower(address.ort)) like lower('Neuss%')
> AND
> bs.value = 'bezahlt' OR bs.value = 'erlassen'
> AND
> users.roles like '%Premium'
> ORDER by person.name
> LIMIT 100
>
> Limit (cost=70.10..70.11 rows=1 width=214)
> -> Sort (cost=70.10..70.11 rows=1 width=214)
> Sort Key: person.name
> -> Hash Join (cost=58.91..70.09 rows=1 width=214)
> Hash Cond: ("outer".id_status = "inner".id)
> Join Filter: ((("inner".value = 'erlassen'::character
> varying) OR ("outer".id_status = 2)) AND (("inner".value =
> 'erlassen'::character varying) OR ("outer".id_authorise = 2)) AND
> (("inner".value = 'erlassen'::character varying) OR
> (ltrim(lower(("outer".ort)::text)) ~~ 'neuss%'::text)) AND (("outer".roles
> ~~ '%Premium'::text) OR ("inner".value = 'bezahlt'::character varying)))
> -> Hash Join (cost=57.80..68.97 rows=1 width=199)
> Hash Cond: ("outer".id_person = "inner".id_person)
> -> Seq Scan on bill (cost=0.00..10.44 rows=144
> width=8)
> -> Hash (cost=57.80..57.80 rows=1 width=191)
> -> Hash Join (cost=49.85..57.80 rows=1
> width=191)
> Hash Cond: ("outer".id_person =
> "inner".id_person)
> Join Filter: ((("inner".roles ~~
> '%Premium'::text) OR ("outer".id_status = 2)) AND (("inner".roles ~~
> '%Premium'::text) OR ("outer".id_authorise = 2)) AND (("inner".roles ~~
> '%Premium'::text) OR (ltrim(lower(("outer".ort)::text)) ~~ 'neuss%'::text)))
> -> Merge Join (cost=41.84..44.05
> rows=153 width=176)
> Merge Cond: ("outer".id_gender =
> "inner".id)
> -> Sort (cost=40.81..41.19
> rows=153 width=164)
> Sort Key: person.id_gender
> -> Hash Join
> (cost=18.97..35.25 rows=153 width=164)
> Hash Cond:
> ("outer".id_person = "inner".id_person)
> -> Seq Scan on address
> (cost=0.00..13.58 rows=158 width=120)
> -> Hash
> (cost=18.59..18.59 rows=153 width=44)
> -> Hash Join
> (cost=4.91..18.59 rows=153 width=44)
> Hash Cond:
> ("outer".id_person = "inner".id_person)
> -> Seq Scan
> on person (cost=0.00..10.58 rows=158 width=40)
> -> Hash
> (cost=4.53..4.53 rows=153 width=4)
> ->
> Seq Scan on therapist (cost=0.00..4.53 rows=153 width=4)
> -> Sort (cost=1.03..1.03 rows=2
> width=12)
> Sort Key: gender.id
> -> Seq Scan on gender_list
> gender (cost=0.00..1.02 rows=2 width=12)
> -> Hash (cost=7.61..7.61 rows=161
> width=15)
> -> Seq Scan on users
> (cost=0.00..7.61 rows=161 width=15)
> -> Hash (cost=1.10..1.10 rows=2 width=15)
> -> Seq Scan on bill_status_list bs (cost=0.00..1.10
> rows=2 width=15)
> Filter: ((value = 'erlassen'::character varying)
> OR (value = 'bezahlt'::character varying))
>
> What I notice is that in the second query the following filter is missing.
> -> Hash (cost=14.77..14.77 rows=1 width=120)
> -> Seq Scan on address (cost=0.00..14.77
> rows=1 width=120)
> Filter: (ltrim(lower((ort)::text))
> ~~ 'neuss%'::text)

It's not missing, it's merely moved. It's become part of the join filter
for the top hash join.

> Join Filter: ((("inner".value = 'erlassen'::character
> varying) OR ("outer".id_status = 2)) AND (("inner".value =
> 'erlassen'::character varying) OR ("outer".id_authorise = 2)) AND
> (("inner".value = 'erlassen'::character varying) OR
> (ltrim(lower(("outer".ort)::text)) ~~ 'neuss%'::text)) AND (("outer".roles
> ~~ '%Premium'::text) OR ("inner".value = 'bezahlt'::character varying)))

This probably means that it thinks that the condition won't push down.

I think perhaps
> bs.value = 'bezahlt' OR bs.value = 'erlassen'
is meant to be
(bs.value = 'bezahlt' OR bs.value='erlassen')