Optimizer Selecting Incorrect Index
| От | David Price |
|---|---|
| Тема | Optimizer Selecting Incorrect Index |
| Дата | |
| Msg-id | OJEEJPPOBPODJFPEOFAGIEBLCGAA.dprice@dentfirst.com обсуждение исходный текст |
| Список | pgsql-sql |
I have 2 servers both with the exact same data, the same O.S., the same
version of Postgres (7.4.5) and the exact same db schema's (one production
server, one development server). One server is using the correct index for
SQL queries resulting in extremely slow performance, the other server is
properly selecting the index to use and performance is many times better. I
have tried vacuum, but that did not work. I finally resorted to dumping the
data, removing the database completely, creating a new database and
importing the data only to have to problem resurface. The table has
5,000,000+ rows on both the systems.
When I run 'analyze verbose' on the correctly working system, the following
is displayed: {INDEXSCAN :startup_cost 0.00 :total_cost 465.10 :plan_rows 44 :plan_width 118 :targetlist (
{TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1
:resnametrn_integer :ressortgroupref 0 :resorigtbl 789839 :resorigcol 1 :resjunk false
}
:expr {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 1 } }
{TARGETENTRY :resdom {RESDOM :resno 2 :restype 23 :restypmod -1
:resnametrn_patno :ressortgroupref 0 :resorigtbl 789839 :resorigcol 2 :resjunk false
}
:expr {VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 2 } }
{TARGETENTRY :resdom {RESDOM :resno 3 :restype 1042 :restypmod 5
:resnametrn_bill_inc :ressortgroupref 0 :resorigtbl 789839 :resorigcol 3 :resjunk false
}
:expr {VAR :varno 1 :varattno 3 :vartype 1042 :vartypmod 5
:varlevelsup0 :varnoold 1 :varoattno 3 } }
{TARGETENTRY :resdom {RESDOM :resno 4 :restype 1043 :restypmod 13
:resnametrn_userid :ressortgroupref 0 :resorigtbl 789839 :resorigcol 4 :resjunk false
}
:expr {VAR :varno 1 :varattno 4 :vartype 1043 :vartypmod 13
:varlevelsup0 :varnoold 1 :varoattno 4 } }
{TARGETENTRY :resdom {RESDOM :resno 5 :restype 23 :restypmod -1
:resnametrn_location :ressortgroupref 0 :resorigtbl 789839 :resorigcol 5 :resjunk false
}
:expr {VAR :varno 1 :varattno 5 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 5 } }
{TARGETENTRY :resdom {RESDOM :resno 6 :restype 1082 :restypmod -1
:resnametrn_date :ressortgroupref 0 :resorigtbl 789839 :resorigcol 6 :resjunk false
} :expr {VAR :varno 1 :varattno 6 :vartype 1082 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 6 } }
{TARGETENTRY :resdom {RESDOM :resno 7 :restype 23 :restypmod -1
:resnametrn_sercode :ressortgroupref 0 :resorigtbl 789839 :resorigcol 7 :resjunk false
}
:expr {VAR :varno 1 :varattno 7 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 7 } }
{TARGETENTRY :resdom {RESDOM :resno 8 :restype 1043 :restypmod 28
:resnametrn_descr :ressortgroupref 0 :resorigtbl 789839 :resorigcol 8 :resjunk false
} :expr {VAR :varno 1 :varattno 8 :vartype 1043 :vartypmod 28
:varlevelsup0 :varnoold 1 :varoattno 8 } }
{TARGETENTRY :resdom {RESDOM :resno 9 :restype 23 :restypmod -1
:resnametrn_employr :ressortgroupref 0 :resorigtbl 789839 :resorigcol 9 :resjunk false
}
:expr {VAR :varno 1 :varattno 9 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 9 } }
{TARGETENTRY :resdom {RESDOM :resno 10 :restype 23 :restypmod -1
:resnametrn_prof :ressortgroupref 0 :resorigtbl 789839 :resorigcol 10 :resjunk false
} :expr {VAR :varno 1 :varattno 10 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 10 } }
{TARGETENTRY :resdom {RESDOM :resno 11 :restype 1700 :restypmod 720902
:resname trn_amount :ressortgroupref 0 :resorigtbl 789839 :resorigcol 11 :resjunk
false }
:expr {VAR :varno 1 :varattno 11 :vartype 1700 :vartypmod 720902
:varlevelsup0 :varnoold 1 :varoattno 11 } }
{TARGETENTRY :resdom {RESDOM :resno 12 :restype 1043 :restypmod 7
:resnametrn_tooth :ressortgroupref 0 :resorigtbl 789839 :resorigcol 12 :resjunk false
} :expr {VAR :varno 1 :varattno 12 :vartype 1043 :vartypmod 7
:varlevelsup0 :varnoold 1 :varoattno 12 } }
{TARGETENTRY :resdom {RESDOM :resno 13 :restype 1043 :restypmod 10
:resnametrn_surface :ressortgroupref 0 :resorigtbl 789839 :resorigcol 13 :resjunk false
}
:expr {VAR :varno 1 :varattno 13 :vartype 1043 :vartypmod 10
:varlevelsup0 :varnoold 1 :varoattno 13 } }
{TARGETENTRY :resdom {RESDOM :resno 14 :restype 1042 :restypmod 5
:resnametrn_flag :ressortgroupref 0 :resorigtbl 789839 :resorigcol 14 :resjunk false
} :expr {VAR :varno 1 :varattno 14 :vartype 1042 :vartypmod 5
:varlevelsup0 :varnoold 1 :varoattno 14 } }
{TARGETENTRY :resdom {RESDOM :resno 15 :restype 23 :restypmod -1
:resnametrn_counter :ressortgroupref 0 :resorigtbl 789839 :resorigcol 15 :resjunk false
}
:expr {VAR :varno 1 :varattno 15 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 15 } }
{TARGETENTRY :resdom {RESDOM :resno 16 :restype 23 :restypmod -1
:resnametrn_guarantr :ressortgroupref 0 :resorigtbl 789839 :resorigcol 16 :resjunk
false } :expr {VAR :varno 1 :varattno 16 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 16 } }
{TARGETENTRY :resdom {RESDOM :resno 17 :restype 1042 :restypmod 5
:resnametrn_lab :ressortgroupref 0 :resorigtbl 789839 :resorigcol 17 :resjunk false
}
:expr {VAR :varno 1 :varattno 17 :vartype 1042 :vartypmod 5
:varlevelsup0 :varnoold 1 :varoattno 17 } }
{TARGETENTRY :resdom {RESDOM :resno 18 :restype 1082 :restypmod -1
:resnametrn_old_date :ressortgroupref 0 :resorigtbl 789839 :resorigcol 18 :resjunk
false } :expr {VAR :varno 1 :varattno 18 :vartype 1082 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno 18 } }
{TARGETENTRY :resdom {RESDOM :resno 19 :restype 1042 :restypmod 5
:resnametrn_hist_flag :ressortgroupref 0 :resorigtbl 789839 :resorigcol 19 :resjunk
false }
:expr {VAR :varno 1 :varattno 19 :vartype 1042 :vartypmod 5
:varlevelsup0 :varnoold 1 :varoattno 19 } }
{TARGETENTRY :resdom {RESDOM :resno 20 :restype 23 :restypmod -1
:resnametrn_check_no :ressortgroupref 0 :resorigtbl 789839 :resorigcol 20 :resjunk
false } :expr {VAR :varno 1 :varattno 20 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 20 } }
{TARGETENTRY :resdom {RESDOM :resno 21 :restype 1043 :restypmod 7
:resnametrn_commcode :ressortgroupref 0 :resorigtbl 789839 :resorigcol 21 :resjunk
false }
:expr {VAR :varno 1 :varattno 21 :vartype 1043 :vartypmod 7
:varlevelsup0 :varnoold 1 :varoattno 21 } } ) :qual ( {OPEXPR :opno 1098
:opfuncid 1090 :opresulttype 16 :opretset false :args ( {VAR :varno 1 :varattno
18 :vartype 1082 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 18
}
{CONST :consttype 1082 :constlen 4 :constbyval true :constisnull false
:constvalue4 [ 91 -8 -1 -1 ] } ) } {OPEXPR :opno 1096 :opfuncid 1088
:opresulttype16 :opretset false :args ( {VAR :varno 1 :varattno 18 :vartype
1082 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 18 }
{CONST :consttype 1082 :constlen 4 :constbyval true :constisnull false
:constvalue4 [ -96 6 0 0 ] } ) }
{OPEXPR :opno 1054 :opfuncid 1048 :opresulttype 16 :opretset false :args ( {VAR
:varno 1 :varattno 3 :vartype 1042 :vartypmod 5 :varlevelsup 0 :varnoold
1 :varoattno 3 }
{CONST :consttype 1042 :constlen -1 :constbyval false :constisnull false
:constvalue 5 [ 5 0 0 0 66 ] } ) } )
:lefttree <> :righttree <> :initPlan <> :extParam ()
:allParam ()
:nParamExec 0 :scanrelid 1 :indxid ( 7725589)
:indxqual (( {OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :args (
{VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold1 :varoattno 2 }
{CONST :consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue4 [ 63 13 3 0 ] } ) } ) )
:indxqualorig (( {OPEXPR :opno 96 :opfuncid 65 :opresulttype 16 :opretset false :args
( {VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 2 }
{CONST :consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue4 [ 63 13 3 0 ] } ) } ) )
:indxorderdir 1 }
Index Scan using trptserc on trans (cost=0.00..465.10 rows=44 width=118) Index Cond: (trn_patno = 199999) Filter:
((trn_old_date>= '1994-08-23'::date) AND (trn_old_date <=
'2004-08-23'::date) AND (trn_bill_inc = 'B'::bpchar))
(687 rows)
Now, when I run 'analyze verbose' on the INCORRECTLY working system, the
following is displayed: {INDEXSCAN :startup_cost 0.00 :total_cost 105165.74 :plan_rows 1 :plan_width 143
:targetlist( {TARGETENTRY :resdom {RESDOM :resno 1 :restype 23 :restypmod -1
:resname trn_integer :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 1
:resjunkfalse }
:expr {VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 1 } }
{TARGETENTRY :resdom {RESDOM :resno 2 :restype 23 :restypmod -1
:resnametrn_patno :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 2 :resjunk false
}
:expr {VAR :varno 1 :varattno 2 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 2 } }
{TARGETENTRY :resdom {RESDOM :resno 3 :restype 1042 :restypmod 5
:resnametrn_bill_inc :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 3 :resjunk
false }
:expr {VAR :varno 1 :varattno 3 :vartype 1042 :vartypmod 5
:varlevelsup0 :varnoold 1 :varoattno 3 } }
{TARGETENTRY :resdom {RESDOM :resno 4 :restype 1043 :restypmod 13
:resnametrn_userid :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 4 :resjunk false
}
:expr {VAR :varno 1 :varattno 4 :vartype 1043 :vartypmod 13
:varlevelsup0 :varnoold 1 :varoattno 4 } }
{TARGETENTRY :resdom {RESDOM :resno 5 :restype 23 :restypmod -1
:resnametrn_location :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 5 :resjunk
false }
:expr {VAR :varno 1 :varattno 5 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 5 } }
{TARGETENTRY :resdom {RESDOM :resno 6 :restype 1082 :restypmod -1
:resnametrn_date :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 6 :resjunk false
}
:expr {VAR :varno 1 :varattno 6 :vartype 1082 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 6 } }
{TARGETENTRY :resdom {RESDOM :resno 7 :restype 23 :restypmod -1
:resnametrn_sercode :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 7 :resjunk false
}
:expr {VAR :varno 1 :varattno 7 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 7 } }
{TARGETENTRY :resdom {RESDOM :resno 8 :restype 1043 :restypmod 28
:resnametrn_descr :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 8 :resjunk false
} :expr {VAR :varno 1 :varattno 8 :vartype 1043 :vartypmod 28
:varlevelsup0 :varnoold 1 :varoattno 8 } }
{TARGETENTRY :resdom {RESDOM :resno 9 :restype 23 :restypmod -1
:resnametrn_employer :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 9 :resjunk
false }
:expr {VAR :varno 1 :varattno 9 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 9 } }
{TARGETENTRY :resdom {RESDOM :resno 10 :restype 23 :restypmod -1
:resnametrn_prof :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 10 :resjunk false
}
:expr {VAR :varno 1 :varattno 10 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 10 } }
{TARGETENTRY :resdom {RESDOM :resno 11 :restype 1700 :restypmod 720902
:resname trn_amount :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 11 :resjunk
false } :expr {VAR :varno 1 :varattno 11 :vartype 1700 :vartypmod
720902 :varlevelsup 0 :varnoold 1 :varoattno 11 } }
{TARGETENTRY :resdom {RESDOM :resno 12 :restype 1043 :restypmod 7
:resnametrn_tooth :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 12 :resjunk false
}
:expr {VAR :varno 1 :varattno 12 :vartype 1043 :vartypmod 7
:varlevelsup0 :varnoold 1 :varoattno 12 } } {TARGETENTRY :resdom {RESDOM
:resno 13 :restype 1043 :restypmod 10 :resname trn_surface :ressortgroupref 0
:resorigtbl 2487466 :resorigcol 13 :resjunk false }
:expr {VAR :varno 1 :varattno 13 :vartype 1043 :vartypmod 10
:varlevelsup0 :varnoold 1 :varoattno 13 } }
{TARGETENTRY :resdom {RESDOM :resno 14 :restype 1042 :restypmod 5
:resnametrn_flag :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 14 :resjunk false
}
:expr {VAR :varno 1 :varattno 14 :vartype 1042 :vartypmod 5
:varlevelsup0 :varnoold 1 :varoattno 14 } }
{TARGETENTRY :resdom {RESDOM :resno 15 :restype 23 :restypmod -1
:resnametrn_counter :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 15 :resjunk
false }
:expr {VAR :varno 1 :varattno 15 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 15 } }
{TARGETENTRY :resdom {RESDOM :resno 16 :restype 23 :restypmod -1
:resnametrn_guarantr :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 16 :resjunk
false }
:expr {VAR :varno 1 :varattno 16 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 16 } }
{TARGETENTRY :resdom {RESDOM :resno 17 :restype 1042 :restypmod 5
:resnametrn_lab :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 17 :resjunk false
}
:expr {VAR :varno 1 :varattno 17 :vartype 1042 :vartypmod 5
:varlevelsup0 :varnoold 1 :varoattno 17 } }
{TARGETENTRY :resdom {RESDOM :resno 18 :restype 1082 :restypmod -1
:resnametrn_old_date :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 18 :resjunk
false }
:expr {VAR :varno 1 :varattno 18 :vartype 1082 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 18 } }
{TARGETENTRY :resdom {RESDOM :resno 19 :restype 1042 :restypmod 5
:resnametrn_hist_flag :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 19 :resjunk
false }
:expr {VAR :varno 1 :varattno 19 :vartype 1042 :vartypmod 5
:varlevelsup0 :varnoold 1 :varoattno 19 } }
{TARGETENTRY :resdom {RESDOM :resno 20 :restype 23 :restypmod -1
:resnametrn_check_no :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 20 :resjunk
false }
:expr {VAR :varno 1 :varattno 20 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 20 } }
{TARGETENTRY :resdom {RESDOM :resno 21 :restype 1043 :restypmod 7
:resnametrn_commcode :ressortgroupref 0 :resorigtbl 2487466 :resorigcol 21 :resjunk
false }
:expr {VAR :varno 1 :varattno 21 :vartype 1043 :vartypmod 7
:varlevelsup0 :varnoold 1 :varoattno 21 } } ) :qual ( {OPEXPR :opno 96
:opfuncid65 :opresulttype 16 :opretset false :args ( {VAR :varno 1 :varattno 2
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 2 }
{CONST :consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue4 [ 63 13 3 0 ] } ) }
{OPEXPR :opno 1054 :opfuncid 1048 :opresulttype 16 :opretset false :args ( {VAR
:varno 1 :varattno 3 :vartype 1042 :vartypmod 5 :varlevelsup 0 :varnoold
1 :varoattno 3 }
{CONST :consttype 1042 :constlen -1 :constbyval false :constisnull false
:constvalue 5 [ 5 0 0 0 66 ] } ) } )
:lefttree <> :righttree <> :initPlan <> :extParam ()
:allParam ()
:nParamExec 0 :scanrelid 1 :indxid ( 7762034)
:indxqual (( {OPEXPR :opno 1098 :opfuncid 1090 :opresulttype 16 :opretset false :args
( {VAR :varno 1 :varattno 1 :vartype 1082 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 18 }
{CONST :consttype 1082 :constlen 4 :constbyval true :constisnull false
:constvalue4 [ 91 -8 -1 -1 ] } ) } {OPEXPR :opno 1096 :opfuncid 1088
:opresulttype16 :opretset false :args ( {VAR :varno 1 :varattno 1 :vartype
1082 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 18 }
{CONST :consttype 1082 :constlen 4 :constbyval true :constisnull false
:constvalue4 [ -96 6 0 0 ] } ) } ) )
:indxqualorig (( {OPEXPR :opno 1098 :opfuncid 1090 :opresulttype 16 :opretset false
:args( {VAR :varno 1 :varattno 18 :vartype 1082 :vartypmod -1
:varlevelsup0 :varnoold 1 :varoattno 18 } {CONST :consttype 1082
:constlen4 :constbyval true :constisnull false :constvalue 4 [ 91 -8 -1 -1 ] } )
}
{OPEXPR :opno 1096 :opfuncid 1088 :opresulttype 16 :opretset false :args ( {VAR
:varno 1 :varattno 18 :vartype 1082 :vartypmod -1 :varlevelsup 0
:varnoold1 :varoattno 18 }
{CONST :consttype 1082 :constlen 4 :constbyval true :constisnull false
:constvalue4 [ -96 6 0 0 ] } ) } ) )
:indxorderdir 1 }
Index Scan using todate on trans (cost=0.00..105165.74 rows=1 width=143) Index Cond: ((trn_old_date >=
'1994-08-23'::date)AND (trn_old_date <=
'2004-08-23'::date)) Filter: ((trn_patno = 199999) AND (trn_bill_inc = 'B'::bpchar))
(713 rows)
So, you see the query optimizer has choosen different indices - one
correctly and the other incorrectly on the exact same set of data???? I can
change the query to reduce the number of arguments and then perform a
subquery (in my java code) but I am afraid there is an internal problem that
will crop up somewhere else. Any insight into this would be appreciated.
В списке pgsql-sql по дате отправления: