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
