Postgres8.0 planner chooses WRONG plan - Mailing list pgsql-performance

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