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@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@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@xxxxxxx> 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
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=231579.22..231579.22 rows=1 width=728) (actual time=31694.695..31694.881 rows=10 loops=1)
-> Hash Left Join (cost=175223.17..231579.22 rows=2 width=728) (actual time=31694.513..31694.847 rows=20 loops=1)
Hash Cond: (residenced19_.residencetype_id = residencet22_.id)
-> Hash Join (cost=175221.97..231577.99 rows=2 width=703) (actual time=31673.089..31673.327 rows=20 loops=1)
Hash Cond: (city1_.associatedgisfeature_id = gf2_.id)
-> Hash Join (cost=172805.64..228974.15 rows=50000 width=546) (actual time=31580.727..31580.879 rows=20 loops=1)
Hash Cond: (residenced19_.city_id = city1_.id)
-> Hash Join (cost=94091.95..130023.46 rows=50000 width=530) (actual time=19588.949..22856.266 rows=50000 loops=1)
Hash Cond: (this_.residencedescription_id = residenced19_.id)
-> Hash Left Join (cost=87371.30..116064.81 rows=50000 width=308) (actual time=17404.025..17644.729 rows=50000 loops=1)
Hash Cond: (contactinf7_.city_id = city8_.id)
-> Hash Left Join (cost=8657.62..20542.12 rows=50000 width=292) (actual time=3894.656..7765.298 rows=50000 loops=1)
Hash Cond: (account6_.contactinformation_id = contactinf7_.id)
-> Hash Left Join (cost=8656.51..19853.50 rows=50000 width=253) (actual time=3894.587..7565.762 rows=50000 loops=1)
Hash Cond: (ace3_.eventinitiator_id = account6_.id)
-> Hash Left Join (cost=8655.40..19403.56 rows=50000 width=192) (actual time=3894.468..7379.405 rows=50000 loops=1)
Hash Cond: (ace3_.id = ace3_1_.id)
-> Hash Join (cost=3591.00..10105.67 rows=50000 width=176) (actual time=3058.596..5371.813 rows=50000 loops=1)
Hash Cond: (ace3_.id = this_.adcreatedevent_id)
-> Seq Scan on adcreatedevent ace3_ (cost=0.00..2323.41 rows=149641 width=16) (actual time=0.038..1452.758 rows=149641 loops=1)
-> Hash (cost=1818.00..1818.00 rows=50000 width=160) (actual time=1793.711..1793.711 rows=50000 loops=1)
-> Seq Scan on roommateresidenceoffer this_ (cost=0.00..1818.00 rows=50000 width=160) (actual time=17.340..1584.689 rows=50000 loops=1)
-> Hash (cost=2389.62..2389.62 rows=149662 width=16) (actual time=835.791..835.791 rows=149662 loops=1)
-> Seq Scan on funalaevent ace3_1_ (cost=0.00..2389.62 rows=149662 width=16) (actual time=103.232..555.443 rows=149662 loops=1)
-> Hash (cost=1.05..1.05 rows=5 width=61) (actual time=0.071..0.071 rows=5 loops=1)
-> Seq Scan on account account6_ (cost=0.00..1.05 rows=5 width=61) (actual time=0.050..0.056 rows=5 loops=1)
-> Hash (cost=1.05..1.05 rows=5 width=39) (actual time=0.046..0.046 rows=5 loops=1)
-> Seq Scan on contactinformation contactinf7_ (cost=0.00..1.05 rows=5 width=39) (actual time=0.025..0.030 rows=5 loops=1)
-> Hash (cost=37037.86..37037.86 rows=2331986 width=16) (actual time=9232.251..9232.251 rows=2331986 loops=1)
-> Seq Scan on city city8_ (cost=0.00..37037.86 rows=2331986 width=16) (actual time=0.043..4694.169 rows=2331986 loops=1)
-> Hash (cost=3365.40..3365.40 rows=77540 width=222) (actual time=2183.568..2183.568 rows=77540 loops=1)
-> Seq Scan on residencedescription residenced19_ (cost=0.00..3365.40 rows=77540 width=222) (actual time=0.033..1875.987 rows=77540 loops=1)
-> Hash (cost=37037.86..37037.86 rows=2331986 width=16) (actual time=8024.211..8024.211 rows=2331986 loops=1)
-> Seq Scan on city city1_ (cost=0.00..37037.86 rows=2331986 width=16) (actual time=0.075..3488.792 rows=2331986 loops=1)
-> Hash (cost=2413.73..2413.73 rows=208 width=157) (actual time=92.330..92.330 rows=1697 loops=1)
-> Bitmap Heap Scan on gisfeature gf2_ (cost=22.54..2413.73 rows=208 width=157) (actual time=58.968..89.167 rows=1697 loops=1)
Filter: (("location" && '0103000020E610000001000000050000009BC810BB85B8F83F01D42B98AA5D48409BC810BB85B8F83F4134414633804840D44ADA6E12F908404134414633804840D44ADA6E12F9084001D42B98AA5D48409BC810BB85B8F83F01D42B98AA5D4840'::geometry) AND (distance_sphere("location", '0101000020E6100000915731A6AAAA0240218436EFEE6E4840'::geometry) <= 15000::double precision))
-> Bitmap Index Scan on gisfeaturelocation (cost=0.00..22.49 rows=625 width=0) (actual time=58.505..58.505 rows=2761 loops=1)
Index Cond: ("location" && '0103000020E610000001000000050000009BC810BB85B8F83F01D42B98AA5D48409BC810BB85B8F83F4134414633804840D44ADA6E12F908404134414633804840D44ADA6E12F9084001D42B98AA5D48409BC810BB85B8F83F01D42B98AA5D4840'::geometry)
-> Hash (cost=1.09..1.09 rows=9 width=25) (actual time=21.261..21.261 rows=9 loops=1)
-> Seq Scan on residencetype residencet22_ (cost=0.00..1.09 rows=9 width=25) (actual time=21.215..21.230 rows=9 loops=1)
Total runtime: 31725.674 ms
(42 lignes)
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3126.79..3126.79 rows=1 width=728) (actual time=0.112..0.112 rows=0 loops=1)
-> Nested Loop (cost=44.85..3126.79 rows=1 width=728) (actual time=0.108..0.108 rows=0 loops=1)
-> Nested Loop Left Join (cost=44.85..3117.78 rows=1 width=571) (actual time=0.106..0.106 rows=0 loops=1)
Join Filter: (residenced19_.residencetype_id = residencet22_.id)
-> Nested Loop Left Join (cost=44.85..3116.58 rows=1 width=546) (actual time=0.104..0.104 rows=0 loops=1)
-> Nested Loop (cost=44.85..3108.96 rows=1 width=530) (actual time=0.102..0.102 rows=0 loops=1)
-> Nested Loop (cost=44.85..3101.06 rows=1 width=514) (actual time=0.100..0.100 rows=0 loops=1)
-> Nested Loop (cost=44.85..3093.44 rows=1 width=292) (actual time=0.098..0.098 rows=0 loops=1)
-> Hash Join (cost=44.85..3085.84 rows=1 width=132) (actual time=0.096..0.096 rows=0 loops=1)
Hash Cond: (ace3_.eventinitiator_id = account6_.id)
-> Seq Scan on adcreatedevent ace3_ (cost=0.00..2323.41 rows=149641 width=16) (actual time=0.030..0.030 rows=1 loops=1)
-> Hash (cost=44.84..44.84 rows=1 width=116) (actual time=0.045..0.045 rows=0 loops=1)
-> Nested Loop (cost=0.00..44.84 rows=1 width=116) (actual time=0.044..0.044 rows=0 loops=1)
Join Filter: (account6_.contactinformation_id = contactinf7_.id)
-> Nested Loop (cost=0.00..43.73 rows=1 width=55) (actual time=0.041..0.041 rows=0 loops=1)
-> Seq Scan on contactinformation contactinf7_ (cost=0.00..1.05 rows=5 width=39) (actual time=0.005..0.014 rows=5 loops=1)
-> Index Scan using cityid on city city8_ (cost=0.00..8.52 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=5)
Index Cond: (contactinf7_.city_id = city8_.id)
-> Seq Scan on account account6_ (cost=0.00..1.05 rows=5 width=61) (never executed)
-> Index Scan using roommateresidenceofferadcreatedevent on roommateresidenceoffer this_ (cost=0.00..7.59 rows=1 width=160) (never executed)
Index Cond: (this_.adcreatedevent_id = ace3_.id)
-> Index Scan using residencedescription_pkey on residencedescription residenced19_ (cost=0.00..7.61 rows=1 width=222) (never executed)
Index Cond: (this_.residencedescription_id = residenced19_.id)
-> Index Scan using cityid on city city1_ (cost=0.00..7.88 rows=1 width=16) (never executed)
Index Cond: (residenced19_.city_id = city1_.id)
-> Index Scan using funalaevent_pkey on funalaevent ace3_1_ (cost=0.00..7.61 rows=1 width=16) (never executed)
Index Cond: (ace3_.id = ace3_1_.id)
-> Seq Scan on residencetype residencet22_ (cost=0.00..1.09 rows=9 width=25) (never executed)
-> Index Scan using gisfeatureid on gisfeature gf2_ (cost=0.00..9.00 rows=1 width=157) (never executed)
Index Cond: (city1_.associatedgisfeature_id = gf2_.id)
Filter: (("location" && '0103000020E610000001000000050000009BC810BB85B8F83F01D42B98AA5D48409BC810BB85B8F83F4134414633804840D44ADA6E12F908404134414633804840D44ADA6E12F9084001D42B98AA5D48409BC810BB85B8F83F01D42B98AA5D4840'::geometry) AND (distance_sphere("location", '0101000020E6100000915731A6AAAA0240218436EFEE6E4840'::geometry) <= 15000::double precision))
Total runtime: 1.137 ms
(32 lignes)
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
