Re: Postgres8.0 planner chooses WRONG plan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi Tom,

Thanks! for your input, the view was written first without using the function but its an ugly big with all the joins and its much slower that way. Below is the view without the function and its explain analzye output , as you can see the it takes almost 2 min to run this query with this view . Is there any way to optimize or make changes to this view ?

Thanks!
Pallav.


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) -> 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) -> 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)



Tom Lane wrote:
Pallav Kalva <pkalva@xxxxxxxxxxxxxxxxx> writes:
  I am having problem optimizing this query,

Get rid of the un-optimizable function inside the view.  You've
converted something that should be a join into an unreasonably large
number of function calls.

-> Seq Scan on serviceinstance si (cost=0.00..15831.52 rows=7 width=78) (actual time=174.430..18948.210 rows=358 loops=1) Filter: (((subplan) = 'FL'::text) AND ((subplan) = '099'::text))
                         SubPlan
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.090..0.093 rows=1 loops=3923) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.058..0.061 rows=1 loops=265617)

The bulk of the cost here is in the second subplan (0.061 * 265617 =
16202.637 msec total runtime), and there's not a darn thing Postgres
can do to improve this because the work is all down inside a "black box"
function.  In fact the planner does not even know that the function call
is expensive, else it would have preferred a plan that requires fewer
evaluations of the function.  The alternative plan you show is *not*
faster "because it's an indexscan"; it's faster because get_parametervalue
is evaluated fewer times.

The useless sub-SELECTs atop the function calls are adding their own
little increment of wasted time, too.  I'm not sure how bad that is
relative to the function calls, but it's certainly not helping.

			regards, tom lane




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux