[Fwd: Re: Outer joins and Seq scans] - Mailing list pgsql-performance
| From | Sami Dalouche |
|---|---|
| Subject | [Fwd: Re: Outer joins and Seq scans] |
| Date | |
| Msg-id | 1193865739.23774.2.camel@samlaptop Whole thread Raw |
| Responses |
Re: [Fwd: Re: Outer joins and Seq scans]
Re: [Fwd: Re: Outer joins and Seq scans] |
| List | pgsql-performance |
-- For some reason, my message doesn't seem to go through the mailing list, so I am trying without any attachment Hi, Thank you Tom and Dimitri for your precious help. So, I applied the patch at http://archives.postgresql.org/pgsql-committers/2007-10/msg00374.php The good news is that with the patch applied, the query is ~3 times faster. The bad news is that it is still WAYYY slower than when using an inner join (~10 sec vs 300 ms) The outer join query 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 left outer join GisFeature gisfeature9_ on city8_.associatedGisFeature_id=gisfeature9_.id left outer join EmailChangedEvent emailchang10_ on contactinf7_.currentEmailChangedEvent_id=emailchang10_.id left outer join FunalaEvent emailchang10_1_ on emailchang10_.id=emailchang10_1_.id left outer join ContactInformation contactinf11_ on emailchang10_.contactInformation_id=contactinf11_.id left outer join EmailCheckedEvent emailcheck12_ on emailchang10_.emailCheckedEvent_id=emailcheck12_.id left outer join FunalaEvent emailcheck12_1_ on emailcheck12_.id=emailcheck12_1_.id left outer join DeclaredAsAdultEvent declaredas13_ on account6_.declaredAsAdultEvent_id=declaredas13_.id left outer join FunalaEvent declaredas13_1_ on declaredas13_.id=declaredas13_1_.id left outer join UserProfile userprofil14_ on account6_.profile_id=userprofil14_.id left outer join AccountSettings accountset15_ on account6_.settings_id=accountset15_.id left outer join AccountCreatedEvent accountcre16_ on account6_.id=accountcre16_.createdAccount_id left outer join FunalaEvent accountcre16_1_ on accountcre16_.id=accountcre16_1_.id left outer join IpAddress ipaddress17_ on accountcre16_.requesterAddress_id=ipaddress17_.id left outer join AccountCancelledEvent accountcan18_ on accountcre16_.id=accountcan18_.accountCreatedEvent_id left outer join FunalaEvent accountcan18_1_ on accountcan18_.id=accountcan18_1_.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 and ace3_1_.utcEventDate>='2007-09-29 00:00:00' order by ace3_1_.utcEventDate asc limit 10; and the full explain analyze output is in exp3.txt (12794,919 ms) the inner join query 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 inner join City city8_ on contactinf7_.city_id=city8_.id left outer join GisFeature gisfeature9_ on city8_.associatedGisFeature_id=gisfeature9_.id left outer join EmailChangedEvent emailchang10_ on contactinf7_.currentEmailChangedEvent_id=emailchang10_.id left outer join FunalaEvent emailchang10_1_ on emailchang10_.id=emailchang10_1_.id left outer join ContactInformation contactinf11_ on emailchang10_.contactInformation_id=contactinf11_.id left outer join EmailCheckedEvent emailcheck12_ on emailchang10_.emailCheckedEvent_id=emailcheck12_.id left outer join FunalaEvent emailcheck12_1_ on emailcheck12_.id=emailcheck12_1_.id left outer join DeclaredAsAdultEvent declaredas13_ on account6_.declaredAsAdultEvent_id=declaredas13_.id left outer join FunalaEvent declaredas13_1_ on declaredas13_.id=declaredas13_1_.id left outer join UserProfile userprofil14_ on account6_.profile_id=userprofil14_.id left outer join AccountSettings accountset15_ on account6_.settings_id=accountset15_.id left outer join AccountCreatedEvent accountcre16_ on account6_.id=accountcre16_.createdAccount_id left outer join FunalaEvent accountcre16_1_ on accountcre16_.id=accountcre16_1_.id left outer join IpAddress ipaddress17_ on accountcre16_.requesterAddress_id=ipaddress17_.id left outer join AccountCancelledEvent accountcan18_ on accountcre16_.id=accountcan18_.accountCreatedEvent_id left outer join FunalaEvent accountcan18_1_ on accountcan18_.id=accountcan18_1_.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 and ace3_1_.utcEventDate>='2007-09-29 00:00:00' order by ace3_1_.utcEventDate asc limit 10; and the full explain analyze output is in exp4.txt (153,220 ms) When comparing the outputs, we can see for instance that Seq Scan on funalaevent ace3_1_ (cost=0.00..2763.78 rows=149653 width=16) (actual time=0.033..271.267 rows=149662 loops=1) (exp3) vs Index Scan using funalaevent_pkey on funalaevent ace3_1_ (exp4) So, there is still something that prevents the indexes from being used (the funalaevent table contains ~ 50 K entries, as much as adcreatedevent. City contains 2 million entries). So any seq scan is awful.... So, is it possible that there is still a similar bug somewhere else ? Thanks Sami Dalouche Le dimanche 28 octobre 2007 à 19:45 -0400, Tom Lane a écrit : > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org ========================= exp3.txt QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=67357.88..67357.89 rows=2 width=3355) (actual time=11686.334..11686.369 rows=10 loops=1) -> Sort (cost=67357.88..67357.89 rows=2 width=3355) (actual time=11686.328..11686.343 rows=10 loops=1) Sort Key: ace3_1_.utceventdate -> Hash Left Join (cost=28098.27..67357.87 rows=2 width=3355) (actual time=4127.714..7944.527 rows=50000 loops=1) Hash Cond: (residenced19_.residencetype_id = residencet22_.id) -> Hash Join (cost=28097.07..67356.64 rows=2 width=3330) (actual time=4127.623..7641.190 rows=50000 loops=1) Hash Cond: (residenced19_.city_id = city1_.id) -> Hash Left Join (cost=23922.95..62995.02 rows=49997 width=3157) (actual time=4064.232..7239.816 rows=50000 loops=1) Hash Cond: (account6_.settings_id = accountset15_.id) -> Hash Left Join (cost=23921.84..62306.45 rows=49997 width=2633) (actual time=4064.157..6956.697 rows=50000 loops=1) Hash Cond: (account6_.id = accountcre16_.createdaccount_id) -> Hash Join (cost=23866.11..61563.26 rows=49997 width=1496) (actual time=4063.758..6664.260 rows=50000 loops=1) Hash Cond: (this_.residencedescription_id = residenced19_.id) -> Hash Left Join (cost=17145.46..35788.66 rows=49997 width=1274) (actual time=3656.249..4750.195 rows=50000 loops=1) Hash Cond: (emailchang10_.contactinformation_id = contactinf11_.id) -> Hash Left Join (cost=17144.35..35100.09 rows=49997 width=1235) (actual time=3656.212..4535.661 rows=50000 loops=1) Hash Cond: (account6_.profile_id = userprofil14_.id) -> Hash Left Join (cost=17143.24..34411.51 rows=49997 width=618) (actual time=3656.143..4284.698 rows=50000 loops=1) Hash Cond: (emailchang10_.emailcheckedevent_id = emailcheck12_.id) -> Hash Left Join (cost=17133.92..34194.71 rows=49997 width=594) (actual time=3656.062..4087.233 rows=50000 loops=1) Hash Cond: (account6_.declaredasadultevent_id = declaredas13_.id) -> Hash Left Join (cost=17131.39..33504.72 rows=49997 width=570) (actual time=3655.699..3895.024 rows=50000 loops=1) Hash Cond: (emailchang10_.id = emailchang10_1_.id) -> Hash Left Join (cost=12067.00..19568.88 rows=49997 width=554) (actual time=1778.375..2762.537 rows=50000 loops=1) Hash Cond: (ace3_.eventinitiator_id = account6_.id) -> Hash Join (cost=11976.67..19029.74 rows=49997 width=192) (actual time=1777.954..2518.385 rows=50000 loops=1) Hash Cond: (ace3_1_.id = this_.adcreatedevent_id) -> Seq Scan on funalaevent ace3_1_ (cost=0.00..2763.78 rows=149653 width=16) (actual time=0.033..271.267 rows=149662 loops=1) Filter: (utceventdate >= '2007-09-29 00:00:00'::timestamp without time zone) -> Hash (cost=10105.67..10105.67 rows=50000 width=176) (actual time=1411.204..1411.204 rows=50000 loops=1) -> Hash Join (cost=3591.00..10105.67 rows=50000 width=176) (actual time=645.641..1183.975 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.019..234.812 rows=149641 loops=1) -> Hash (cost=1818.00..1818.00 rows=50000 width=160) (actual time=295.946..295.946 rows=50000 loops=1) -> Seq Scan on roommateresidenceoffer this_ (cost=0.00..1818.00 rows=50000 width=160) (actual time=0.037..101.503 rows=50000 loops=1) -> Hash (cost=90.27..90.27 rows=5 width=362) (actual time=0.369..0.369 rows=5 loops=1) -> Nested Loop Left Join (cost=2.11..90.27 rows=5 width=362) (actual time=0.141..0.344 rows=5 loops=1) -> Nested Loop Left Join (cost=2.11..46.96 rows=5 width=205) (actual time=0.131..0.313 rows=5 loops=1) -> Nested Loop Left Join (cost=2.11..4.29 rows=5 width=189) (actual time=0.115..0.275 rows=5 loops=1) Join Filter: (contactinf7_.currentemailchangedevent_id = emailchang10_.id) -> Nested Loop Left Join (cost=1.05..2.67 rows=5 width=100) (actual time=0.056..0.152 rows=5 loops=1) Join Filter: (account6_.contactinformation_id = contactinf7_.id) -> Seq Scan on account account6_ (cost=0.00..1.05 rows=5 width=61) (actual time=0.030..0.035 rows=5 loops=1) -> Materialize (cost=1.05..1.10 rows=5 width=39) (actual time=0.004..0.011 rows=5 loops=5) -> Seq Scan on contactinformation contactinf7_ (cost=0.00..1.05 rows=5 width=39) (actual time=0.006..0.012 rows=5 loops=1) -> Materialize (cost=1.05..1.10 rows=5 width=89) (actual time=0.006..0.014 rows=5 loops=5) -> Seq Scan on emailchangedevent emailchang10_ (cost=0.00..1.05 rows=5 width=89) (actual time=0.021..0.028 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) -> Index Scan using gisfeatureid on gisfeature gisfeature9_ (cost=0.00..8.65 rows=1 width=157) (actual time=0.001..0.001 rows=0 loops=5) Index Cond: (city8_.associatedgisfeature_id = gisfeature9_.id) -> Hash (cost=2389.62..2389.62 rows=149662 width=16) (actual time=496.535..496.535 rows=149662 loops=1) -> Seq Scan on funalaevent emailchang10_1_ (cost=0.00..2389.62 rows=149662 width=16) (actual time=0.016..216.256 rows=149662 loops=1) -> Hash (cost=2.46..2.46 rows=5 width=24) (actual time=0.300..0.300 rows=5 loops=1) -> Merge Right Join (cost=1.11..2.46 rows=5 width=24) (actual time=0.215..0.285 rows=5 loops=1) Merge Cond: (declaredas13_1_.id = declaredas13_.id) -> Index Scan using funalaevent_pkey on funalaevent declaredas13_1_ (cost=0.00..4809.35 rows=149662 width=16) (actual time=0.121..0.149 rows=22 loops=1) -> Sort (cost=1.11..1.12 rows=5 width=8) (actual time=0.071..0.076 rows=5 loops=1) Sort Key: declaredas13_.id -> Seq Scan on declaredasadultevent declaredas13_ (cost=0.00..1.05 rows=5 width=8) (actual time=0.034..0.041 rows=5 loops=1) -> Hash (cost=9.31..9.31 rows=1 width=24) (actual time=0.065..0.065 rows=1 loops=1) -> Nested Loop Left Join (cost=0.00..9.31 rows=1 width=24) (actual time=0.054..0.060 rows=1 loops=1) -> Seq Scan on emailcheckedevent emailcheck12_ (cost=0.00..1.01 rows=1 width=8) (actual time=0.025..0.027 rows=1 loops=1) -> Index Scan using funalaevent_pkey on funalaevent emailcheck12_1_ (cost=0.00..8.28 rows=1 width=16) (actual time=0.016..0.018 rows=1 loops=1) Index Cond: (emailcheck12_.id = emailcheck12_1_.id) -> Hash (cost=1.05..1.05 rows=5 width=617) (actual time=0.047..0.047 rows=5 loops=1) -> Seq Scan on userprofile userprofil14_ (cost=0.00..1.05 rows=5 width=617) (actual time=0.027..0.033 rows=5 loops=1) -> Hash (cost=1.05..1.05 rows=5 width=39) (actual time=0.021..0.021 rows=5 loops=1) -> Seq Scan on contactinformation contactinf11_ (cost=0.00..1.05 rows=5 width=39) (actual time=0.005..0.011 rows=5 loops=1) -> Hash (cost=3365.40..3365.40 rows=77540 width=222) (actual time=405.248..405.248 rows=77540 loops=1) -> Seq Scan on residencedescription residenced19_ (cost=0.00..3365.40 rows=77540 width=222) (actual time=0.048..157.678 rows=77540 loops=1) -> Hash (cost=55.66..55.66 rows=5 width=1137) (actual time=0.367..0.367 rows=5 loops=1) -> Nested Loop Left Join (cost=13.80..55.66 rows=5 width=1137) (actual time=0.228..0.347 rows=5 loops=1) -> Hash Left Join (cost=13.80..15.32 rows=5 width=1121) (actual time=0.215..0.312 rows=5 loops=1) Hash Cond: (accountcre16_.id = accountcan18_.accountcreatedevent_id) -> Hash Left Join (cost=2.22..3.68 rows=5 width=73) (actual time=0.183..0.269 rows=5 loops=1) Hash Cond: (accountcre16_.requesteraddress_id = ipaddress17_.id) -> Merge Right Join (cost=1.11..2.50 rows=5 width=40) (actual time=0.117..0.189 rows=5 loops=1) Merge Cond: (accountcre16_1_.id = accountcre16_.id) -> Index Scan using funalaevent_pkey on funalaevent accountcre16_1_ (cost=0.00..4809.35 rows=149662 width=16) (actual time=0.029..0.059 rows=24 loops=1) -> Sort (cost=1.11..1.12 rows=5 width=24) (actual time=0.068..0.073 rows=5 loops=1) Sort Key: accountcre16_.id -> Seq Scan on accountcreatedevent accountcre16_ (cost=0.00..1.05 rows=5 width=24) (actual time=0.039..0.044 rows=5 loops=1) -> Hash (cost=1.05..1.05 rows=5 width=33) (actual time=0.044..0.044 rows=5 loops=1) -> Seq Scan on ipaddress ipaddress17_ (cost=0.00..1.05 rows=5 width=33) (actual time=0.024..0.030 rows=5 loops=1) -> Hash (cost=10.70..10.70 rows=70 width=1048) (actual time=0.004..0.004 rows=0 loops=1) -> Seq Scan on accountcancelledevent accountcan18_ (cost=0.00..10.70 rows=70 width=1048) (actual time=0.002..0.002 rows=0 loops=1) -> Index Scan using funalaevent_pkey on funalaevent accountcan18_1_ (cost=0.00..8.06 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=5) Index Cond: (accountcan18_.id = accountcan18_1_.id) -> Hash (cost=1.05..1.05 rows=5 width=524) (actual time=0.050..0.050 rows=5 loops=1) -> Seq Scan on accountsettings accountset15_ (cost=0.00..1.05 rows=5 width=524) (actual time=0.028..0.034 rows=5 loops=1) -> Hash (cost=4173.19..4173.19 rows=74 width=173) (actual time=63.358..63.358 rows=137 loops=1) -> Nested Loop (cost=22.54..4173.19 rows=74 width=173) (actual time=4.611..62.608 rows=137 loops=1) -> Bitmap Heap Scan on gisfeature gf2_ (cost=22.54..2413.73 rows=208 width=157) (actual time=4.517..32.198 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=4.109..4.109 rows=2761 loops=1) Index Cond: ("location" && '0103000020E610000001000000050000009BC810BB85B8F83F01D42B98AA5D48409BC810BB85B8F83F4134414633804840D44ADA6E12F908404134414633804840D44ADA6E12F9084001D42B98AA5D48409BC810BB85B8F83F01D42B98AA5D4840'::geometry) -> Index Scan using cityassociatedgisfeatureid on city city1_ (cost=0.00..8.45 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1697) Index Cond: (city1_.associatedgisfeature_id = gf2_.id) -> Hash (cost=1.09..1.09 rows=9 width=25) (actual time=0.065..0.065 rows=9 loops=1) -> Seq Scan on residencetype residencet22_ (cost=0.00..1.09 rows=9 width=25) (actual time=0.030..0.042 rows=9 loops=1) Total runtime: 12366.311 ms (102 lignes) =========================== exp4.txt: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=3198.68..3198.69 rows=1 width=3355) (actual time=0.189..0.189 rows=0 loops=1) -> Sort (cost=3198.68..3198.69 rows=1 width=3355) (actual time=0.184..0.184 rows=0 loops=1) Sort Key: ace3_1_.utceventdate -> Nested Loop (cost=44.85..3198.67 rows=1 width=3355) (actual time=0.153..0.153 rows=0 loops=1) -> Nested Loop (cost=44.85..3190.00 rows=1 width=3198) (actual time=0.149..0.149 rows=0 loops=1) -> Nested Loop Left Join (cost=44.85..3181.47 rows=1 width=3182) (actual time=0.147..0.147 rows=0 loops=1) Join Filter: (residenced19_.residencetype_id = residencet22_.id) -> Nested Loop Left Join (cost=44.85..3180.26 rows=1 width=3157) (actual time=0.145..0.145 rows=0 loops=1) Join Filter: (account6_.settings_id = accountset15_.id) -> Nested Loop Left Join (cost=44.85..3179.15 rows=1 width=2633) (actual time=0.143..0.143 rows=0 loops=1) -> Nested Loop Left Join (cost=44.85..3170.85 rows=1 width=2617) (actual time=0.140..0.140 rows=0 loops=1) Join Filter: (accountcre16_.requesteraddress_id = ipaddress17_.id) -> Nested Loop Left Join (cost=44.85..3169.74 rows=1 width=2584) (actual time=0.139..0.139 rows=0 loops=1) -> Nested Loop Left Join (cost=44.85..3161.67 rows=1 width=2568) (actual time=0.136..0.136 rows=0 loops=1) Join Filter: (accountcre16_.id = accountcan18_.accountcreatedevent_id) -> Nested Loop Left Join (cost=44.85..3150.10 rows=1 width=1520) (actual time=0.133..0.133 rows=0 loops=1) Join Filter: (account6_.id = accountcre16_.createdaccount_id) -> Nested Loop (cost=44.85..3148.99 rows=1 width=1496) (actual time=0.132..0.132 rows=0 loops=1) -> Nested Loop Left Join (cost=44.85..3140.70 rows=1 width=1274) (actual time=0.129..0.129 rows=0 loops=1) -> Nested Loop Left Join (cost=44.85..3132.40 rows=1 width=1258) (actual time=0.126..0.126 rows=0 loops=1) Join Filter: (emailchang10_.contactinformation_id = contactinf11_.id) -> Nested Loop Left Join (cost=44.85..3131.29 rows=1 width=1219) (actual time=0.125..0.125 rows=0 loops=1) -> Nested Loop Left Join (cost=44.85..3122.99 rows=1 width=1203) (actual time=0.122..0.122 rows=0 loops=1) Join Filter: (emailchang10_.emailcheckedevent_id = emailcheck12_.id) -> Nested Loop Left Join (cost=44.85..3121.97 rows=1 width=1195) (actual time=0.120..0.120 rows=0 loops=1) -> Nested Loop Left Join (cost=44.85..3113.67 rows=1 width=1179) (actual time=0.118..0.118 rows=0 loops=1) Join Filter: (account6_.declaredasadultevent_id = declaredas13_.id) -> Nested Loop Left Join (cost=44.85..3112.56 rows=1 width=1171) (actual time=0.115..0.115 rows=0 loops=1) Join Filter: (account6_.profile_id = userprofil14_.id) -> Nested Loop Left Join (cost=44.85..3111.45 rows=1 width=554) (actual time=0.112..0.112 rows=0 loops=1) -> Nested Loop Left Join (cost=44.85..3102.79 rows=1 width=397) (actual time=0.110..0.110 rows=0 loops=1) Join Filter: (contactinf7_.currentemailchangedevent_id = emailchang10_.id) -> Nested Loop (cost=44.85..3101.67 rows=1 width=308) (actual time=0.108..0.108 rows=0 loops=1) -> Nested Loop (cost=44.85..3093.77 rows=1 width=148) (actual time=0.106..0.106 rows=0 loops=1) -> Hash Join (cost=44.85..3085.84 rows=1 width=132) (actual time=0.104..0.104 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.034..0.034 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.043..0.043 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.040..0.040 rows=0 loops=1) -> Seq Scan on contactinformation contactinf7_ (cost=0.00..1.05 rows=5 width=39) (actual time=0.004..0.012 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 funalaevent_pkey on funalaevent ace3_1_ (cost=0.00..7.92 rows=1 width=16) (never executed) Index Cond: (ace3_.id = ace3_1_.id) Filter: (utceventdate >= '2007-09-29 00:00:00'::timestamp without time zone) -> Index Scan using roommateresidenceofferadcreatedevent on roommateresidenceoffer this_ (cost=0.00..7.89 rows=1 width=160) (never executed) Index Cond: (this_.adcreatedevent_id = ace3_.id) -> Seq Scan on emailchangedevent emailchang10_ (cost=0.00..1.05 rows=5 width=89) (never executed) -> Index Scan using gisfeatureid on gisfeature gisfeature9_ (cost=0.00..8.65 rows=1 width=157) (never executed) Index Cond: (city8_.associatedgisfeature_id = gisfeature9_.id) -> Seq Scan on userprofile userprofil14_ (cost=0.00..1.05 rows=5 width=617) (never executed) -> Seq Scan on declaredasadultevent declaredas13_ (cost=0.00..1.05 rows=5 width=8) (never executed) -> Index Scan using funalaevent_pkey on funalaevent declaredas13_1_ (cost=0.00..8.28 rows=1 width=16) (never executed) Index Cond: (declaredas13_.id = declaredas13_1_.id) -> Seq Scan on emailcheckedevent emailcheck12_ (cost=0.00..1.01 rows=1 width=8) (never executed) -> Index Scan using funalaevent_pkey on funalaevent emailcheck12_1_ (cost=0.00..8.28 rows=1 width=16) (never executed) Index Cond: (emailcheck12_.id = emailcheck12_1_.id) -> Seq Scan on contactinformation contactinf11_ (cost=0.00..1.05 rows=5 width=39) (never executed) -> Index Scan using funalaevent_pkey on funalaevent emailchang10_1_ (cost=0.00..8.28 rows=1 width=16) (never executed) Index Cond: (emailchang10_.id = emailchang10_1_.id) -> Index Scan using residencedescription_pkey on residencedescription residenced19_ (cost=0.00..8.28 rows=1 width=222) (never executed) Index Cond: (this_.residencedescription_id = residenced19_.id) -> Seq Scan on accountcreatedevent accountcre16_ (cost=0.00..1.05 rows=5 width=24) (never executed) -> Seq Scan on accountcancelledevent accountcan18_ (cost=0.00..10.70 rows=70 width=1048) (never executed) -> Index Scan using funalaevent_pkey on funalaevent accountcan18_1_ (cost=0.00..8.06 rows=1 width=16) (never executed) Index Cond: (accountcan18_.id = accountcan18_1_.id) -> Seq Scan on ipaddress ipaddress17_ (cost=0.00..1.05 rows=5 width=33) (never executed) -> Index Scan using funalaevent_pkey on funalaevent accountcre16_1_ (cost=0.00..8.28 rows=1 width=16) (never executed) Index Cond: (accountcre16_.id = accountcre16_1_.id) -> Seq Scan on accountsettings accountset15_ (cost=0.00..1.05 rows=5 width=524) (never executed) -> Seq Scan on residencetype residencet22_ (cost=0.00..1.09 rows=9 width=25) (never executed) -> Index Scan using cityid on city city1_ (cost=0.00..8.52 rows=1 width=16) (never executed) Index Cond: (residenced19_.city_id = city1_.id) -> Index Scan using gisfeatureid on gisfeature gf2_ (cost=0.00..8.66 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: 25.647 ms (80 lignes)
pgsql-performance by date: