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

Re: Outer joins and Seq scans

From: Sami Dalouche <skoobi(at)free(dot)fr>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Outer joins and Seq scans
Date: 2007-10-28 22:55:46
Message-ID: 1193612147.9809.13.camel@samlaptop (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

Sorry for not giving enough information.. I didn't want to pollute you
with too much detail... 

So, the version of postgres I use is :
samokk(at)samlaptop:~/Desktop $ dpkg -l | grep postgres
ii  postgresql-8.2                             8.2.5-1.1
object-relational SQL database, version 8.2 
ii  postgresql-8.2-postgis                     1.2.1-2
geographic objects support for PostgreSQL 8.
ii  postgresql-client-8.2                      8.2.5-1.1
front-end programs for PostgreSQL 8.2
ii  postgresql-client-common                   78
manager for multiple PostgreSQL client versi
ii  postgresql-common                          78
manager for PostgreSQL database clusters
ii  postgresql-contrib-8.2                     8.2.5-1.1
additional facilities for PostgreSQL

samokk(at)samlaptop:~/Desktop $ uname -a
Linux samlaptop 2.6.22-14-generic #1 SMP Sun Oct 14 23:05:12 GMT 2007
i686 GNU/Linux

The exact query that is run is :

select * from RoommateResidenceOffer this_ inner join AdCreatedEvent
ace3_ on this_.adCreatedEvent_id=ace3_.id left outer join FunalaEvent
ace3_1_ on ace3_.id=ace3_1_.id left outer join Account account6_ on
ace3_.eventInitiator_id=account6_.id left outer join ContactInformation
contactinf7_ on account6_.contactInformation_id=contactinf7_.id  left
outer join City city8_ on contactinf7_.city_id=city8_.id inner join
ResidenceDescription residenced19_ on
this_.residenceDescription_id=residenced19_.id inner join City city1_ on
residenced19_.city_id=city1_.id inner join GisFeature gf2_ on
city1_.associatedGisFeature_id=gf2_.id left outer join ResidenceType
residencet22_ on residenced19_.residenceType_id=residencet22_.id where
gf2_.location && setSRID(cast ('BOX3D(1.5450494105576016
48.73176862850233,3.1216171894423983 49.00156477149768)'as box3d), 4326)
AND distance_sphere(gf2_.location, GeomFromText('POINT(2.3333333
48.8666667)',4326)) <=  15000 limit 10 offset 10

The full Explain Analyze  for this query is attached in "exp1.txt".

---- 

The slightly modified version of the query above, using inner join
instead of outer join for outer join City city8_ on
contactinf7_.city_id=city8_.id

select * from RoommateResidenceOffer this_ inner join AdCreatedEvent
ace3_ on this_.adCreatedEvent_id=ace3_.id left outer join FunalaEvent
ace3_1_ on ace3_.id=ace3_1_.id left outer join Account account6_ on
ace3_.eventInitiator_id=account6_.id left outer join ContactInformation
contactinf7_ on account6_.contactInformation_id=contactinf7_.id  inner
join City city8_ on contactinf7_.city_id=city8_.id inner join
ResidenceDescription residenced19_ on
this_.residenceDescription_id=residenced19_.id inner join City city1_ on
residenced19_.city_id=city1_.id inner join GisFeature gf2_ on
city1_.associatedGisFeature_id=gf2_.id left outer join ResidenceType
residencet22_ on residenced19_.residenceType_id=residencet22_.id where
gf2_.location && setSRID(cast ('BOX3D(1.5450494105576016
48.73176862850233,3.1216171894423983 49.00156477149768)'as box3d), 4326)
AND distance_sphere(gf2_.location, GeomFromText('POINT(2.3333333
48.8666667)',4326)) <=  15000 limit 10 offset 10

The full explain analyze is included in exp2.txt.

So, now the part of the schema that is relevant :

                    Table « public.roommateresidenceoffer »
                Colonne                |          Type          |
Modificateurs 
---------------------------------------+------------------------+---------------
 id                                    | bigint                 | not
null
 endofavailabilitydate                 | date                   | 
 minimumleasedurationinmonths          | integer                | not
null
 brokerfees                            | numeric(19,2)          | not
null
 currencycode                          | character varying(255) | not
null
 monthlyadditionalchargesapproximation | numeric(19,2)          | not
null
 monthlybaseprice                      | numeric(19,2)          | not
null
 pricingperiod                         | character varying(255) | not
null
 securitydeposit                       | numeric(19,2)          | not
null
 startofavailabilitydate               | date                   | not
null
 sublease                              | boolean                | not
null
 cabletv                               | boolean                | not
null
 electricity                           | boolean                | not
null
 heat                                  | boolean                | not
null
 highspeedinternetaccess               | boolean                | not
null
 phoneline                             | boolean                | not
null
 satellitetv                           | boolean                | not
null
 securitysystem                        | boolean                | not
null
 trashpickup                           | boolean                | not
null
 unlimitedphoneplan                    | boolean                | not
null
 water                                 | boolean                | not
null
 offerdescriptiontext                  | text                   | 
 totalnumberofroommates                | integer                | not
null
 willhaveseparateroom                  | boolean                | not
null
 adcreatedevent_id                     | bigint                 | 
 residencedescription_id               | bigint                 | 
Index :
    « roommateresidenceoffer_pkey » PRIMARY KEY, btree (id)
    « roommateresidenceofferadcreatedevent » btree (adcreatedevent_id)
Contraintes de clés étrangères :
    « fk27b7359611df9610 » FOREIGN KEY (adcreatedevent_id) REFERENCES
adcreatedevent(id)
    « fk27b73596364f1d0 » FOREIGN KEY (residencedescription_id)
REFERENCES residencedescription(id)

sirika_development=# \d adcreatedevent
      Table « public.adcreatedevent »
      Colonne      |  Type  | Modificateurs 
-------------------+--------+---------------
 id                | bigint | not null
 eventinitiator_id | bigint | 
Index :
    « adcreatedevent_pkey » PRIMARY KEY, btree (id)
Contraintes de clés étrangères :
    « fk4422475278f7361 » FOREIGN KEY (id) REFERENCES funalaevent(id)
    « fk4422475e7e1a3f5 » FOREIGN KEY (eventinitiator_id) REFERENCES
account(id)


sirika_development=# \d funalaevent
                Table « public.funalaevent »
   Colonne    |            Type             | Modificateurs 
--------------+-----------------------------+---------------
 id           | bigint                      | not null
 utceventdate | timestamp without time zone | not null
Index :
    « funalaevent_pkey » PRIMARY KEY, btree (id)
    « funalaeventdate » btree (utceventdate)


\d Account;
                     Table « public.account »
         Colonne         |          Type          | Modificateurs 
-------------------------+------------------------+---------------
 id                      | bigint                 | not null
 login                   | character varying(255) | not null
 password                | character varying(255) | not null
 settings_id             | bigint                 | 
 profile_id              | bigint                 | 
 declaredasadultevent_id | bigint                 | 
 contactinformation_id   | bigint                 | 
Index :
    « account_pkey » PRIMARY KEY, btree (id)
    « account_login_key » UNIQUE, btree ("login")
Contraintes de clés étrangères :
    « fk1d0c220d3f80c97 » FOREIGN KEY (declaredasadultevent_id)
REFERENCES declaredasadultevent(id)
    « fk1d0c220d7c918a2a » FOREIGN KEY (settings_id) REFERENCES
accountsettings(id)
    « fk1d0c220d95133e52 » FOREIGN KEY (profile_id) REFERENCES
userprofile(id)
    « fk1d0c220ddfd5cd37 » FOREIGN KEY (contactinformation_id)
REFERENCES contactinformation(id)


 \d ContactInformation
                 Table « public.contactinformation »
           Colonne           |          Type          | Modificateurs 
-----------------------------+------------------------+---------------
 id                          | bigint                 | not null
 street                      | text                   | 
 zipcode                     | text                   | 
 name                        | character varying(255) | 
 currentemailchangedevent_id | bigint                 | 
 city_id                     | bigint                 | 
Index :
    « contactinformation_pkey » PRIMARY KEY, btree (id)
    « contactinformationcity » btree (city_id)
    « contactinformationcurrentemailchangedevent » btree
(currentemailchangedevent_id)
Contraintes de clés étrangères :
    « fk36e2e10c6412f2ff » FOREIGN KEY (city_id) REFERENCES city(id)
    « fk36e2e10cb79b5056 » FOREIGN KEY (currentemailchangedevent_id)
REFERENCES emailchangedevent(id)


\d City
              Table « public.city »
         Colonne         |  Type  | Modificateurs 
-------------------------+--------+---------------
 id                      | bigint | not null
 associatedgisfeature_id | bigint | 
Index :
    « city_pkey » PRIMARY KEY, btree (id)
    « cityassociatedgisfeatureid » btree (associatedgisfeature_id)
    « cityid » btree (id)
Contraintes de clés étrangères :
    « fk200d8b1020e199 » FOREIGN KEY (associatedgisfeature_id)
REFERENCES gisfeature(id)


              Table « public.residencedescription »
            Colonne            |       Type       | Modificateurs 
-------------------------------+------------------+---------------
 id                            | bigint           | not null
 street                        | text             | 
 zipcode                       | text             | 
 barbecueandpicnicarea         | boolean          | not null
 basketballcourt               | boolean          | not null
 bikeshelter                   | boolean          | not null
 billiards                     | boolean          | not null
 clubhouse                     | boolean          | not null
 conferenceroom                | boolean          | not null
 doorman                       | boolean          | not null
 drycleaner                    | boolean          | not null
 fitnesscenter                 | boolean          | not null
 gatedentrance                 | boolean          | not null
 laundryfacility               | boolean          | not null
 onsitemanagement              | boolean          | not null
 pool                          | boolean          | not null
 sauna                         | boolean          | not null
 spa                           | boolean          | not null
 tenniscourt                   | boolean          | not null
 residencedescriptiontext      | text             | 
 extrastorage                  | boolean          | not null
 privatepool                   | boolean          | not null
 privatesauna                  | boolean          | not null
 privatespa                    | boolean          | not null
 airconditioning               | boolean          | not null
 areainsquaremeters            | double precision | 
 balcony                       | boolean          | not null
 basefloornumber               | integer          | 
 buzzer                        | boolean          | not null
 ceilingfan                    | boolean          | not null
 dishwasher                    | boolean          | not null
 disposal                      | boolean          | not null
 dryer                         | boolean          | not null
 dvd                           | boolean          | not null
 elevator                      | boolean          | not null
 firedetector                  | boolean          | not null
 fireplace                     | boolean          | not null
 fullkitchen                   | boolean          | not null
 furnished                     | boolean          | not null
 hifi                          | boolean          | not null
 housewaresinkitchen           | boolean          | not null
 microwave                     | boolean          | not null
 numberofbathrooms             | integer          | 
 numberofbedrooms              | integer          | 
 numberoflivingrooms           | integer          | 
 numberofseparatedtoilets      | integer          | 
 oven                          | boolean          | not null
 patio                         | boolean          | not null
 refrigerator                  | boolean          | not null
 stove                         | boolean          | not null
 totalnumberoffloorsinbuilding | integer          | 
 tv                            | boolean          | not null
 vcr                           | boolean          | not null
 washer                        | boolean          | not null
 wheelchairaccess              | boolean          | not null
 yard                          | boolean          | not null
 coveredparkingspaces          | integer          | not null
 garage                        | boolean          | not null
 streetparkingavailability     | bigint           | 
 uncoveredparkingspaces        | integer          | not null
 petsallowed                   | boolean          | not null
 smokingallowed                | boolean          | not null
 city_id                       | bigint           | 
 residencetype_id              | bigint           | 
Index :
    « residencedescription_pkey » PRIMARY KEY, btree (id)
    « residencedescriptioncity » btree (city_id)
Contraintes de clés étrangères :
    « fk997d05366412f2ff » FOREIGN KEY (city_id) REFERENCES city(id)
    « fk997d0536a3749aa4 » FOREIGN KEY (residencetype_id) REFERENCES
residencetype(id)

\d gisfeature
                      Table « public.gisfeature »
         Colonne         |            Type             | Modificateurs 
-------------------------+-----------------------------+---------------
 id                      | bigint                      | not null
 asciiname               | character varying(255)      | 
 elevation               | bigint                      | 
 featureclass            | character varying(255)      | 
 featurecode             | character varying(255)      | 
 featureid               | bigint                      | not null
 featuresource           | character varying(255)      | not null
 gtopo30averageelevation | bigint                      | 
 location                | geometry                    | 
 modificationdate        | timestamp without time zone | 
 name                    | character varying(255)      | 
 population              | bigint                      | 
 timezone                | character varying(255)      | 
 parententity_id         | bigint                      | 
Index :
    « gisfeature_pkey » PRIMARY KEY, btree (id)
    « gisfeatureasciiname » btree (asciiname)
    « gisfeaturefeatureid » btree (featureid)
    « gisfeaturefeaturesource » btree (featuresource)
    « gisfeatureid » btree (id)
    « gisfeaturelocation » gist ("location")
    « gisfeaturenamestartswith » btree (lower(name::text)
varchar_pattern_ops)
    « gisfeatureparententityid » btree (parententity_id)
    « gisfeaturepopulation » btree (population)
Contraintes de clés étrangères :
    « fk6372220511a389a5 » FOREIGN KEY (parententity_id) REFERENCES
abstractadministrativeentity(id)

 \d residencetype
                Table « public.residencetype »
       Colonne       |          Type          | Modificateurs 
---------------------+------------------------+---------------
 id                  | bigint                 | not null
 code                | character varying(255) | not null
 residenceattachment | integer                | 
Index :
    « residencetype_pkey » PRIMARY KEY, btree (id)
    « residencetype_code_key » UNIQUE, btree (code)

If you need more information for your diagnostic, do not hesitate to
request ;-) 
Thanks a lot for your help,
Sami Dalouche


Le dimanche 28 octobre 2007 à 18:08 -0400, Tom Lane a écrit :
> Sami Dalouche <skoobi(at)free(dot)fr> writes:
> > So, what could prevent postgrs from using the index ?
> 
> You've carefully withheld all the details that might let us guess.
> If I had to guess anyway, I'd guess this is a pre-8.2 PG release
> that doesn't know how to rearrange outer joins, but there are any
> number of other possibilities.
> 
> If you want useful help on a query planning issue, you generally need
> to provide
> 	- the exact Postgres version
> 	- full schema declaration of the relevant tables
> 	- exact queries tested
> 	- full EXPLAIN ANALYZE output
> With less info than that, people are just shooting in the dark.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

Attachment: exp2.txt
Description: text/plain (4.6 KB)
Attachment: exp1.txt
Description: text/plain (6.5 KB)

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-10-28 23:45:14
Subject: Re: Outer joins and Seq scans
Previous:From: Tom LaneDate: 2007-10-28 22:08:34
Subject: Re: Outer joins and Seq scans

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