pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOuter joins and Seq scans
7+ messages / 5 participants
[nested] [flat]
* Outer joins and Seq scans
@ 2007-10-28 21:25 Sami Dalouche <skoobi@free.fr>
2007-10-28 22:08 ` Re: Outer joins and Seq scans Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Sami Dalouche @ 2007-10-28 21:25 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a query that uses left outer join, and this seems to prevent the
index on the right column to be used.
I couldn't really trim down the query without having the index used
normally..
So, I have the following tables that join :
Offer -> AdCreatedEvent -> Account -> ContactInformation -> City ->
GisFeature
and
Offer -> ResidenceDescription -> City -> GisFeature
The query is at the end of the email.
What happens is that the "ContactInformation -> City " outer join link
prevents postgres from using the index on City.
If I only join offer -> ResidenceDescription -> City -> GisFeature, the
index is used;
If I join everything in the query without the GIS condition, the index
is used :
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
Also, if I replace the ContactInformation -> City link by an inner
join, the index is used.
With the outer join,
explain analyze tells me :
Hash (cost=37037.86..37037.86 rows=2331986 width=16)
-> Seq Scan on city city1_
(cost=0.00..37037.86 rows=2331986 width=16)
Whereas the inner join tells me :
-> Index Scan using cityid on city city8_ (cost=0.00..8.52 rows=1
width=16)
So, what could prevent postgrs from using the index ? I ran all the
vacuum analyze stuff, and the stats cannot possibly tell postgres that
it's not worth using the index (2 million entries in the city table, way
more than in any other table).
Which options do I have to force postgres to use an index here ?
Thanks for your help,
Sami Dalouche
---------------
select * from Offer 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Outer joins and Seq scans
2007-10-28 21:25 Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
@ 2007-10-28 22:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-28 22:55 ` Re: Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-10-28 22:08 UTC (permalink / raw)
To: Sami Dalouche <skoobi@free.fr>; +Cc: pgsql-performance
Sami Dalouche <skoobi@free.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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Outer joins and Seq scans
2007-10-28 21:25 Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
2007-10-28 22:08 ` Re: Outer joins and Seq scans Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-10-28 22:55 ` Sami Dalouche <skoobi@free.fr>
2007-10-28 23:45 ` Re: Outer joins and Seq scans Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Sami Dalouche @ 2007-10-28 22:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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@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@free.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
Attachments:
exp1.txt (text/plain, 6.5K) download
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)
exp2.txt (text/plain, 4.6K) download
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)
* Re: Outer joins and Seq scans
2007-10-28 21:25 Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
2007-10-28 22:08 ` Re: Outer joins and Seq scans Tom Lane <tgl@sss.pgh.pa.us>
2007-10-28 22:55 ` Re: Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
@ 2007-10-28 23:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-29 09:48 ` Re: Outer joins and Seq scans Dimitri Fontaine <dfontaine@hi-media.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-10-28 23:45 UTC (permalink / raw)
To: Sami Dalouche <skoobi@free.fr>; +Cc: pgsql-performance
Sami Dalouche <skoobi@free.fr> writes:
> So, the version of postgres I use is :
> samokk@samlaptop:~/Desktop $ dpkg -l | grep postgres
> ii postgresql-8.2 8.2.5-1.1
OK. I think you have run afoul of a bug that was introduced in 8.2.5
that causes it not to realize that it can interchange the ordering of
certain outer joins. Is there any chance you can apply the one-line
patch shown here:
http://archives.postgresql.org/pgsql-committers/2007-10/msg00374.php
If rebuilding packages is not to your taste, possibly a down-rev to
8.2.4 would be the easiest solution.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Outer joins and Seq scans
2007-10-28 21:25 Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
2007-10-28 22:08 ` Re: Outer joins and Seq scans Tom Lane <tgl@sss.pgh.pa.us>
2007-10-28 22:55 ` Re: Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
2007-10-28 23:45 ` Re: Outer joins and Seq scans Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-10-29 09:48 ` Dimitri Fontaine <dfontaine@hi-media.com>
2007-10-29 11:11 ` Re: Outer joins and Seq scans Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dimitri Fontaine @ 2007-10-29 09:48 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Sami Dalouche <skoobi@free.fr>
Hi,
Le lundi 29 octobre 2007, Tom Lane a écrit :
> Is there any chance you can apply the one-line
> patch shown here:
> http://archives.postgresql.org/pgsql-committers/2007-10/msg00374.php
>
> If rebuilding packages is not to your taste, possibly a down-rev to
> 8.2.4 would be the easiest solution.
The debian package for PostgreSQL uses a .tar.gz of the upstream code along
with a debian/patches/ directory with ordered patches files
(##-whatever.patch). Just adding the given file into this directory before to
building the package should do.
The operations to issue should look like this:
$ apt-get source postgresql-8.2
$ tar xzf postgresql-8.2_8.2.5.orig.tar.gz
$ cd postgresql-8.2-8.2.5
$ zcat ../postgresql-8.2_8.2.5-1.diff.gz |patch -p1
$ cp make_outerjoininfo.patch debian/patches/60-make_outerjoininfo.patch
$ debuild -us -uc
This will give you a new package for postgresql, which you can even tweak the
version number to your taste by editing debian/changelog and adding (for
example) a postgresql-8.2 (8.2.5.1-1) entry, just before the debuild step.
Hope this helps,
--
dim
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Outer joins and Seq scans
2007-10-28 21:25 Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
2007-10-28 22:08 ` Re: Outer joins and Seq scans Tom Lane <tgl@sss.pgh.pa.us>
2007-10-28 22:55 ` Re: Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
2007-10-28 23:45 ` Re: Outer joins and Seq scans Tom Lane <tgl@sss.pgh.pa.us>
2007-10-29 09:48 ` Re: Outer joins and Seq scans Dimitri Fontaine <dfontaine@hi-media.com>
@ 2007-10-29 11:11 ` Richard Huxton <dev@archonet.com>
2007-10-29 11:28 ` Re: Outer joins and Seq scans Cédric Villemain <cedric.villemain@dalibo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2007-10-29 11:11 UTC (permalink / raw)
To: Dimitri Fontaine <dfontaine@hi-media.com>; +Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Sami Dalouche <skoobi@free.fr>
Dimitri Fontaine wrote:
> Hi,
>
> Le lundi 29 octobre 2007, Tom Lane a écrit :
>> Is there any chance you can apply the one-line
>> patch shown here:
>> http://archives.postgresql.org/pgsql-committers/2007-10/msg00374.php
>>
>> If rebuilding packages is not to your taste, possibly a down-rev to
>> 8.2.4 would be the easiest solution.
>
> The debian package for PostgreSQL uses a .tar.gz of the upstream code along
> with a debian/patches/ directory with ordered patches files
> (##-whatever.patch). Just adding the given file into this directory before to
> building the package should do.
[snip detailed example]
It never occurred to me that you could do that. Thanks Dmitri, I learn
something here all the time.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Outer joins and Seq scans
2007-10-28 21:25 Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
2007-10-28 22:08 ` Re: Outer joins and Seq scans Tom Lane <tgl@sss.pgh.pa.us>
2007-10-28 22:55 ` Re: Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
2007-10-28 23:45 ` Re: Outer joins and Seq scans Tom Lane <tgl@sss.pgh.pa.us>
2007-10-29 09:48 ` Re: Outer joins and Seq scans Dimitri Fontaine <dfontaine@hi-media.com>
2007-10-29 11:11 ` Re: Outer joins and Seq scans Richard Huxton <dev@archonet.com>
@ 2007-10-29 11:28 ` Cédric Villemain <cedric.villemain@dalibo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Cédric Villemain @ 2007-10-29 11:28 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Dimitri Fontaine <dfontaine@hi-media.com>; pgsql-performance@postgresql.org, Sami Dalouche <skoobi@free.fr>
Richard Huxton a écrit :
> Dimitri Fontaine wrote:
>> Hi,
>>
>> Le lundi 29 octobre 2007, Tom Lane a écrit :
>>> Is there any chance you can apply the one-line
>>> patch shown here:
>>> http://archives.postgresql.org/pgsql-committers/2007-10/msg00374.php
>>>
>>> If rebuilding packages is not to your taste, possibly a down-rev to
>>> 8.2.4 would be the easiest solution.
>>
>> The debian package for PostgreSQL uses a .tar.gz of the upstream code
>> along with a debian/patches/ directory with ordered patches files
>> (##-whatever.patch). Just adding the given file into this directory
>> before to building the package should do.
>
> [snip detailed example]
>
> It never occurred to me that you could do that. Thanks Dmitri, I learn
> something here all the time.
<more>
You can consider using apt-build(like gentoo users like to do) to patch
on the fly (but it does not support tar.gz inside package, wich is
unfortunely the case for postgresql package ....)
</more>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-10-29 11:28 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-28 21:25 Outer joins and Seq scans Sami Dalouche <skoobi@free.fr>
2007-10-28 22:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-28 22:55 ` Sami Dalouche <skoobi@free.fr>
2007-10-28 23:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-29 09:48 ` Dimitri Fontaine <dfontaine@hi-media.com>
2007-10-29 11:11 ` Richard Huxton <dev@archonet.com>
2007-10-29 11:28 ` Cédric Villemain <cedric.villemain@dalibo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox