Re: Postgres8.0 planner chooses WRONG plan

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(at)livedatagroup(dot)com> 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
>
>