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