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

[Fwd: Re: Outer joins and Seq scans]

From: Sami Dalouche <skoobi(at)free(dot)fr>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: [Fwd: Re: Outer joins and Seq scans]
Date: 2007-10-31 21:22:19
Message-ID: 1193865739.23774.2.camel@samlaptop (view raw or flat)
Thread:
Lists: 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(at)free(dot)fr> writes:
> > 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
> 
> 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)



Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2007-10-31 21:54:26
Subject: Re: Hardware for PostgreSQL
Previous:From: Pablo AlcarazDate: 2007-10-31 20:15:34
Subject: Re: tables with 300+ partitions

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