Thread: Postgres8.0 planner chooses WRONG plan
Hi ,
I am having problem optimizing this query, Postgres optimizer uses a
plan which invloves seq-scan on a table. And when I choose a option to
disable seq-scan it uses index-scan and obviously the query is much faster.
All tables are daily vacummed and analyzed as per docs.
Why cant postgres use index-scan ?
Postgres Version:8.0.2
Platform : Fedora
Here is the explain analyze output. Let me know if any more information
is needed. Can we make postgres use index scan for this query ?
Thanks!
Pallav.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
explain analyze
select * from provisioning.alerts where countystate = 'FL' and countyno
= '099' and status = 'ACTIVE' ;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=3.45..15842.17 rows=1 width=125) (actual
time=913.491..18992.009 rows=110 loops=1)
-> Nested Loop (cost=3.45..15838.88 rows=1 width=86) (actual
time=913.127..18958.482 rows=110 loops=1)
-> Hash Join (cost=3.45..15835.05 rows=1 width=82) (actual
time=913.093..18954.951 rows=110 loops=1)
Hash Cond: ("outer".fkserviceinstancestatusid =
"inner".serviceinstancestatusid)
-> Hash Join (cost=2.38..15833.96 rows=2 width=74)
(actual time=175.139..18952.830 rows=358 loops=1)
Hash Cond: ("outer".fkserviceofferingid =
"inner".serviceofferingid)
-> 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)
-> Hash (cost=2.38..2.38 rows=3 width=4) (actual
time=0.444..0.444 rows=0 loops=1)
-> Hash Join (cost=1.08..2.38 rows=3
width=4) (actual time=0.312..0.428 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.005..0.068 rows=18
loops=1)
-> Hash (cost=1.07..1.07 rows=1
width=4) (actual time=0.036..0.036 rows=0 loops=1)
-> Seq Scan on service s
(cost=0.00..1.07 rows=1 width=4) (actual time=0.014..0.019 rows=1 loops=1)
Filter: (servicename =
'alert'::text)
-> Hash (cost=1.06..1.06 rows=1 width=16) (actual
time=0.044..0.044 rows=0 loops=1)
-> Seq Scan on serviceinstancestatus sis
(cost=0.00..1.06 rows=1 width=16) (actual time=0.017..0.024 rows=1 loops=1)
Filter: (status = 'ACTIVE'::text)
-> Index Scan using pk_account_accountid on account a
(cost=0.00..3.82 rows=1 width=8) (actual time=0.012..0.016 rows=1
loops=110)
Index Cond: ("outer".fkaccountid = a.accountid)
-> Index Scan using pk_contact_contactid on contact c
(cost=0.00..3.24 rows=1 width=47) (actual time=0.014..0.018 rows=1
loops=110)
Index Cond: ("outer".fkcontactid = c.contactid)
SubPlan
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.072..0.075 rows=1 loops=110)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.079..0.082 rows=1 loops=110)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.086..0.089 rows=1 loops=110)
Total runtime: 18992.694 ms
(30 rows)
Time: 18996.203 ms
--> As you can see the -> Seq Scan on serviceinstance si
(cost=0.00..15831.52 rows=7 width=78) (actual time=174.430..18948.210
rows=358 loops=1) was taking too long .
same query when i disable the seq-scan it uses index-scan and its
much faster now
set enable_seqscan=false;
SET
Time: 0.508 ms
explain analyze
select * from provisioning.alerts where countystate = 'FL' and countyno
= '099' and status = 'ACTIVE' ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=9.10..16676.10 rows=1 width=125) (actual
time=24.792..3898.939 rows=110 loops=1)
-> Nested Loop (cost=9.10..16672.81 rows=1 width=86) (actual
time=24.383..3862.025 rows=110 loops=1)
-> Hash Join (cost=9.10..16668.97 rows=1 width=82) (actual
time=24.351..3858.351 rows=110 loops=1)
Hash Cond: ("outer".fkserviceofferingid =
"inner".serviceofferingid)
-> Nested Loop (cost=0.00..16659.85 rows=2 width=86)
(actual time=8.449..3841.260 rows=110 loops=1)
-> Index Scan using
pk_serviceinstancestatus_serviceinstancestatusid on
serviceinstancestatus sis (cost=0.00..3.07 rows=1 width=16) (actual
time=3.673..3.684 rows=1 loops=1)
Filter: (status = 'ACTIVE'::text)
-> Index Scan using
idx_serviceinstance_fkserviceinstancestatusid on serviceinstance si
(cost=0.00..16656.76 rows=2 width=78) (actual time=4.755..3836.399
rows=110 loops=1)
Index Cond: (si.fkserviceinstancestatusid =
"outer".serviceinstancestatusid)
Filter: (((subplan) = 'FL'::text) AND
((subplan) = '099'::text))
SubPlan
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.125..0.128 rows=1 loops=1283)
-> Result (cost=0.00..0.01 rows=1 width=0)
(actual time=0.083..0.086 rows=1 loops=26146)
-> Hash (cost=9.09..9.09 rows=3 width=4) (actual
time=15.661..15.661 rows=0 loops=1)
-> Nested Loop (cost=0.00..9.09 rows=3 width=4)
(actual time=15.617..15.637 rows=1 loops=1)
-> Index Scan using uk_service_servicename on
service s (cost=0.00..3.96 rows=1 width=4) (actual time=11.231..11.236
rows=1 loops=1)
Index Cond: (servicename = 'alert'::text)
-> Index Scan using
idx_serviceoffering_fkserviceid on serviceoffering so (cost=0.00..5.09
rows=3 width=8) (actual time=4.366..4.371 rows=1 loops=1)
Index Cond: ("outer".serviceid =
so.fkserviceid)
-> Index Scan using pk_account_accountid on account a
(cost=0.00..3.82 rows=1 width=8) (actual time=0.013..0.017 rows=1
loops=110)
Index Cond: ("outer".fkaccountid = a.accountid)
-> Index Scan using pk_contact_contactid on contact c
(cost=0.00..3.24 rows=1 width=47) (actual time=0.013..0.017 rows=1
loops=110)
Index Cond: ("outer".fkcontactid = c.contactid)
SubPlan
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.081..0.084 rows=1 loops=110)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.088..0.091 rows=1 loops=110)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.098..0.101 rows=1 loops=110)
Total runtime: 3899.589 ms
(28 rows)
Here is the view definition
-------------------------------
View "provisioning.alerts"
Column | Type | Modifiers
-------------------+---------+-----------
serviceinstanceid | integer |
accountid | integer |
firstname | text |
lastname | text |
email | text |
status | text |
affiliate | text |
affiliatesub | text |
domain | text |
countyno | text |
countystate | text |
listingtype | text |
View definition:
SELECT si.serviceinstanceid, a.accountid, c.firstname, c.lastname,
c.email, sis.status, si.affiliate, si.affiliatesub, si."domain",
( SELECT get_parametervalue(si.serviceinstanceid,
'countyNo'::text) AS get_parametervalue) AS countyno,
( SELECT get_parametervalue(si.serviceinstanceid,
'countyState'::text) AS get_parametervalue) AS countystate,
( SELECT get_parametervalue(si.serviceinstanceid,
'listingType'::text) AS get_parametervalue) AS listingtype
FROM provisioning.account a, common.contact c, provisioning.service s,
provisioning.serviceoffering so, provisioning.serviceinstance si,
provisioning.serviceinstancestatus sis
WHERE si.fkserviceofferingid = so.serviceofferingid
AND si.fkserviceinstancestatusid = sis.serviceinstancestatusid
AND s.serviceid = so.fkserviceid
AND a.fkcontactid = c.contactid
AND si.fkaccountid = a.accountid
AND s.servicename = 'alert'::text;
Function Definition
----------------------
CREATE OR REPLACE FUNCTION get_parametervalue(v_fkserviceinstanceid
integer, v_name text) RETURNS TEXT AS $$
DECLARE
v_value text;
BEGIN
SELECT p.value
INTO v_value
FROM provisioning.serviceinstanceparameter sip,
common.parameter p
WHERE fkserviceinstanceid = v_fkserviceinstanceid
AND sip.fkparameterid = p.parameterid
AND p.name = v_name;
RETURN v_value;
END
Serviceinstance table stats
-----------------------------
select relname, relpages, reltuples from pg_class where relname =
'serviceinstance';
relname | relpages | reltuples
-----------------+----------+-----------
serviceinstance | 5207 | 265613
$$ language plpgsql
Pallav Kalva <pkalva@livedatagroup.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
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@livedatagroup.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
>
>
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