Re: Postgres8.0 planner chooses WRONG plan - Mailing list pgsql-performance

On Wed, Jan 11, 2006 at 11:44:58AM -0500, Pallav Kalva wrote:
Some view you've got there... you might want to break that apart into
multiple views that are a bit easier to manage.
service_instance_with_status is a likely candidate, for example.

> View Definition
> -------------------
>
> create or replace view provisioning.alertserviceinstanceold as
> SELECT services.serviceinstanceid, a.accountid, c.firstname, c.lastname,
> c.email, services.countyno, services.countystate, services.listingtype
> AS listingtypename, services.status, services.affiliate,
> services.affiliatesub, services."domain"
>   FROM provisioning.account a
>   JOIN common.contact c ON a.fkcontactid = c.contactid
>   JOIN ( SELECT p1.serviceinstanceid, p1.accountid, p1.countyno,
> p2.countystate, p3.listingtype, p1.status, p1.affiliate,
> p1.affiliatesub, p1."domain"
>      FROM ( SELECT si.serviceinstanceid, si.affiliate, si.affiliatesub,
> si."domain", si.fkaccountid AS accountid, p.value AS countyno, sis.status
>              FROM provisioning.service s
>         JOIN provisioning.serviceoffering so ON s.serviceid =
> so.fkserviceid
>    JOIN provisioning.serviceinstance si ON so.serviceofferingid =
> si.fkserviceofferingid
>   JOIN provisioning.serviceinstancestatus sis ON
> si.fkserviceinstancestatusid = sis.serviceinstancestatusid
>   JOIN provisioning.serviceinstanceparameter sip ON
> si.serviceinstanceid = sip.fkserviceinstanceid
>   JOIN common.parameter p ON sip.fkparameterid = p.parameterid
>  WHERE s.servicename = 'alert'::text AND p.name = 'countyNo'::text) p1
>   JOIN ( SELECT si.serviceinstanceid, si.affiliate, si.affiliatesub,
> si."domain", si.fkaccountid AS accountid, p.value AS countystate, sis.status
>              FROM provisioning.service s
>         JOIN provisioning.serviceoffering so ON s.serviceid =
> so.fkserviceid
>    JOIN provisioning.serviceinstance si ON so.serviceofferingid =
> si.fkserviceofferingid
>   JOIN provisioning.serviceinstancestatus sis ON
> si.fkserviceinstancestatusid = sis.serviceinstancestatusid
>   JOIN provisioning.serviceinstanceparameter sip ON
> si.serviceinstanceid = sip.fkserviceinstanceid
>   JOIN common.parameter p ON sip.fkparameterid = p.parameterid
>  WHERE s.servicename = 'alert'::text AND p.name = 'countyState'::text)
> p2 ON p1.accountid = p2.accountid AND p1.serviceinstanceid =
> p2.serviceinstanceid
>   JOIN ( SELECT si.serviceinstanceid, si.affiliate, si.affiliatesub,
> si."domain", si.fkaccountid AS accountid, p.value AS listingtype, sis.status
>         FROM provisioning.service s
>    JOIN provisioning.serviceoffering so ON s.serviceid = so.fkserviceid
>   JOIN provisioning.serviceinstance si ON so.serviceofferingid =
> si.fkserviceofferingid
>   JOIN provisioning.serviceinstancestatus sis ON
> si.fkserviceinstancestatusid = sis.serviceinstancestatusid
>   JOIN provisioning.serviceinstanceparameter sip ON
> si.serviceinstanceid = sip.fkserviceinstanceid
>   JOIN common.parameter p ON sip.fkparameterid = p.parameterid
>  WHERE s.servicename = 'alert'::text AND p.name = 'listingType'::text)
> p3 ON p2.accountid = p3.accountid AND p2.serviceinstanceid =
> p3.serviceinstanceid) services
> ON a.accountid = services.accountid
> ORDER BY services.serviceinstanceid;
>
> Explain Analyze
> ------------------
> explain analyze
> select * from provisioning.alertserviceinstanceold where countystate =
> 'FL' and countyno = '099' and status = 'ACTIVE' ;
>
>
     
> QUERY PLAN
>
>
----------------------------------------------------------------------------------------------------------------------------------------------
> --------------------------------------------------------------------------------------------------------------------
> Subquery Scan alertserviceinstanceold  (cost=31954.24..31954.25 rows=1
> width=328) (actual time=113485.801..113487.024 rows=110 loops=1)
>   ->  Sort  (cost=31954.24..31954.24 rows=1 width=152) (actual
> time=113485.787..113486.123 rows=110 loops=1)
>         Sort Key: si.serviceinstanceid
>         ->  Hash Join  (cost=20636.38..31954.23 rows=1 width=152)
> (actual time=109721.688..113485.311 rows=110 loops=1)
>               Hash Cond: ("outer".accountid = "inner".fkaccountid)
>               ->  Hash Join  (cost=6595.89..16770.25 rows=228696
> width=47) (actual time=1742.592..4828.396 rows=229855 loops=1)
>                     Hash Cond: ("outer".contactid = "inner".fkcontactid)
>                     ->  Seq Scan on contact c  (cost=0.00..4456.96
> rows=228696 width=47) (actual time=0.006..1106.459 rows=229868 loops=1)
>                     ->  Hash  (cost=6024.11..6024.11 rows=228711
> width=8) (actual time=1742.373..1742.373 rows=0 loops=1)
>                           ->  Seq Scan on account a
> (cost=0.00..6024.11 rows=228711 width=8) (actual time=0.010..990.597
> rows=229855 loops=1)
>               ->  Hash  (cost=14040.49..14040.49 rows=1 width=117)
> (actual time=107911.397..107911.397 rows=0 loops=1)
>                     ->  Nested Loop  (cost=10.34..14040.49 rows=1
> width=117) (actual time=1185.383..107910.738 rows=110 loops=1)
>                           ->  Nested Loop  (cost=10.34..14037.45 rows=1
> width=112) (actual time=1185.278..107898.885 rows=550 loops=1)
>                                 ->  Hash Join  (cost=10.34..14033.98
> rows=1 width=124) (actual time=1185.224..107888.542 rows=110 loops=1)
>                                       Hash Cond:
> ("outer".fkserviceofferingid = "inner".serviceofferingid)
>                                       ->  Hash Join
> (cost=7.96..14031.58 rows=1 width=128) (actual time=1184.490..107886.329
> rows=110 loops=1)
>                                             Hash Cond:
> ("outer".fkserviceinstancestatusid = "inner".serviceinstancestatusid)
>                                             ->  Nested Loop
> (cost=6.90..14030.50 rows=1 width=132) (actual time=1184.151..107884.302
> rows=110 loops=1)
>                                                   Join Filter:
> ("outer".fkaccountid = "inner".fkaccountid)

Well, here's the step that's killing you:
>                                                   ->  Nested Loop
> (cost=6.90..14025.09 rows=1 width=116) (actual time=1184.123..107880.635
> rows=110 loops=1)
>                                                         Join Filter:
> (("outer".fkaccountid = "inner".fkaccountid) AND
> ("outer".serviceinstanceid = "inner".serviceinstanceid))
>                                                         ->  Hash Join
> (cost=3.45..636.39 rows=1 width=95) (actual time=85.524..293.387
> rows=226 loops=1)
>                                                               Hash
> Cond: ("outer".fkserviceinstancestatusid = "inner".serviceinstancestatusid)

Unfortunately, the way this query plan came out it's difficult to figure
out what the other input to that nested loop is. But before we get to
that, what do you have join_collapse_limit set to? If it's the default
of 8 then the optimizer is essentially going to follow the join order
you specified when you wrote the view, which could be far from optimal.
It would be worth setting join_collapse_limit high enough so that this
query will get flattened and see what kind of plan it comes up with
then. Note that this could result in an unreasonably-large plan time,
but if it results in a fast query execution we know it's just a matter
of re-ordering things in the query.

Also, it would be best if you could send the results of explain as an
attachement that hasn't been word-wrapped.

>                                                               ->  Hash
> Join  (cost=2.38..635.29 rows=4 width=87) (actual time=6.894..289.000
> rows=663 loops=1)
>
> Hash Cond: ("outer".fkserviceofferingid = "inner".serviceofferingid)
>                                                                     ->
> Nested Loop  (cost=0.00..632.75 rows=23 width=91) (actual
> time=6.176..281.620 rows=663 loops=1)
>
> ->  Nested Loop  (cost=0.00..508.26 rows=23 width=13) (actual
> time=6.138..221.590 rows=663 loops=1)
>
> ->  Index Scan using idx_parameter_value on parameter p
> (cost=0.00..437.42 rows=23 width=13) (actual time=6.091..20.656 rows=663
> loops=1)
>
> Index Cond: (value = '099'::text)
>
> Filter: (name = 'countyNo'::text)
>
> ->  Index Scan using idx_serviceinstanceparameter_fkparameterid on
> serviceinstanceparameter sip  (cost=0.00..3.07 rows=1 width=8) (actual
> time=0.278..0.288 rows=1 loops=663)
>
> Index Cond: (sip.fkparameterid = "outer".parameterid)
>
> ->  Index Scan using pk_serviceinstance_serviceinstanceid on
> serviceinstance si  (cost=0.00..5.40 rows=1 width=78) (actual
> time=0.041..0.073 rows=1 loops=663)
>
> Index Cond: (si.serviceinstanceid = "outer".fkserviceinstanceid)
>                                                                     ->
> Hash  (cost=2.38..2.38 rows=3 width=4) (actual time=0.445..0.445 rows=0
> loops=1)
>
> ->  Hash Join  (cost=1.08..2.38 rows=3 width=4) (actual
> time=0.314..0.426 rows=1 loops=1)
>
> Hash Cond: ("outer".fkserviceid = "inner".serviceid)
>
> ->  Seq Scan on serviceoffering so  (cost=0.00..1.18 rows=18width=8)
> (actual time=0.005..0.065 rows=18 loops=1)
>
> ->  Hash  (cost=1.07..1.07 rows=1 width=4) (actual time=0.033..0.033
> rows=0 loops=1)
>
> ->  Seq Scan on service s  (cost=0.00..1.07 rows=1 width=4) (actual
> time=0.011..0.016 rows=1 loops=1)
>
> Filter: (servicename = 'alert'::text)
>                                                               ->  Hash
> (cost=1.06..1.06 rows=1 width=16) (actual time=0.031..0.031 rows=0 loops=1)
>                                                                     ->
> Seq Scan on serviceinstancestatus sis  (cost=0.00..1.06 rows=1 width=16)
> (actual time=0.008..0.014 rows=1 loops=1)
>
> Filter: (status = 'ACTIVE'::text)
>                                                         ->  Hash Join
> (cost=3.45..13386.23 rows=165 width=21) (actual time=0.119..461.891
> rows=3935 loops=226)
>                                                               Hash
> Cond: ("outer".fkserviceinstancestatusid = "inner".serviceinstancestatusid)
>                                                               ->  Hash
> Join  (cost=2.38..13382.69 rows=165 width=25) (actual
> time=0.110..432.555 rows=3935 loops=226)
>
> Hash Cond: ("outer".fkserviceofferingid = "inner".serviceofferingid)
>                                                                     ->
> Nested Loop  (cost=0.00..13373.71 rows=990 width=29) (actual
> time=0.098..400.805 rows=3935 loops=226)
>
> ->  Nested Loop  (cost=0.00..8015.16 rows=990 width=13) (actual
> time=0.035..267.634 rows=3935 loops=226)
>
> ->  Seq Scan on parameter p  (cost=0.00..4968.81 rows=989 width=13)
> (actual time=0.008..131.735 rows=3935 loops=226)
>
> Filter: ((name = 'countyState'::text) AND (value = 'FL'::text))
>
> ->  Index Scan using idx_serviceinstanceparameter_fkparameterid on
> serviceinstanceparameter sip  (cost=0.00..3.07 rows=1 width=8) (actual
> time=0.015..0.020 rows=1 loops=889310)
>
> Index Cond: (sip.fkparameterid = "outer".parameterid)
>
> ->  Index Scan using pk_serviceinstance_serviceinstanceid on
> serviceinstance si  (cost=0.00..5.40 rows=1 width=16) (actual
> time=0.012..0.019 rows=1 loops=889310)
>
> Index Cond: (si.serviceinstanceid = "outer".fkserviceinstanceid)
>                                                                     ->
> Hash  (cost=2.38..2.38 rows=3 width=4) (actual time=0.439..0.439 rows=0
> loops=1)
>
> ->  Hash Join  (cost=1.08..2.38 rows=3 width=4) (actual
> time=0.310..0.423 rows=1 loops=1)
>
> Hash Cond: ("outer".fkserviceid = "inner".serviceid)
>
> ->  Seq Scan on serviceoffering so  (cost=0.00..1.18 rows=18 width=8)
> (actual time=0.006..0.065 rows=18 loops=1)
>
> ->  Hash  (cost=1.07..1.07 rows=1 width=4) (actual time=0.035..0.035
> rows=0 loops=1)
>
> ->  Seq Scan on service s  (cost=0.00..1.07 rows=1 width=4) (actual
> time=0.013..0.018 rows=1 loops=1)
>
> Filter: (servicename = 'alert'::text)
>                                                               ->  Hash
> (cost=1.05..1.05 rows=5 width=4) (actual time=0.059..0.059 rows=0 loops=1)
>                                                                     ->
> Seq Scan on serviceinstancestatus sis  (cost=0.00..1.05 rows=5 width=4)
> (actual time=0.010..0.029 rows=5 loops=1)
>                                                   ->  Index Scan using
> pk_serviceinstance_serviceinstanceid on serviceinstance si
> (cost=0.00..5.40 rows=1 width=16) (actual time=0.009..0.012 rows=1
> loops=110)
>                                                         Index Cond:
> (si.serviceinstanceid = "outer".fkserviceinstanceid)
>                                             ->  Hash  (cost=1.05..1.05
> rows=5 width=4) (actual time=0.055..0.055 rows=0 loops=1)
>                                                   ->  Seq Scan on
> serviceinstancestatus sis  (cost=0.00..1.05 rows=5 width=4) (actual
> time=0.008..0.025 rows=5 loops=1)
>                                       ->  Hash  (cost=2.38..2.38 rows=3
> width=4) (actual time=0.461..0.461 rows=0 loops=1)
>                                             ->  Hash Join
> (cost=1.08..2.38 rows=3 width=4) (actual time=0.325..0.445 rows=1 loops=1)
>                                                   Hash Cond:
> ("outer".fkserviceid = "inner".serviceid)
>                                                   ->  Seq Scan on
> serviceoffering so  (cost=0.00..1.18 rows=18 width=8) (actual
> time=0.006..0.074 rows=18 loops=1)
>                                                   ->  Hash
> (cost=1.07..1.07 rows=1 width=4) (actual time=0.044..0.044 rows=0 loops=1)
>                                                         ->  Seq Scan on
> service s  (cost=0.00..1.07 rows=1 width=4) (actual time=0.022..0.027
> rows=1 loops=1)
>                                                               Filter:
> (servicename = 'alert'::text)
>                                 ->  Index Scan using
> idx_serviceinstanceparameter_fkserviceinstanceid on
> serviceinstanceparameter sip  (cost=0.00..3.41 rows=5 width=8) (actual
> time=0.018..0.038 rows=5 loops=110)
>                                       Index Cond:
> (sip.fkserviceinstanceid = "outer".fkserviceinstanceid)
>                           ->  Index Scan using pk_parameter_parameterid
> on parameter p  (cost=0.00..3.02 rows=1 width=13) (actual
> time=0.011..0.012 rows=0 loops=550)
>                                 Index Cond: ("outer".fkparameterid =
> p.parameterid)
>                                 Filter: (name = 'listingType'::text)
>
> Total runtime: 113490.582 ms
> (82 rows)
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461