Postgres8.0 Planner chooses WRONG plan.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux