Thread: Optimizer Selecting Incorrect Index
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
:resname trn_integer
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 1
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname trn_patno
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 1042
:restypmod 5
:resname trn_bill_inc
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 3
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 3
:vartype 1042
:vartypmod 5
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 1043
:restypmod 13
:resname trn_userid
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 4
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 4
:vartype 1043
:vartypmod 13
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname trn_location
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 5
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 1082
:restypmod -1
:resname trn_date
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 6
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 6
:vartype 1082
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 7
:restype 23
:restypmod -1
:resname trn_sercode
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 7
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 7
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 7
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 8
:restype 1043
:restypmod 28
:resname trn_descr
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 8
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 8
:vartype 1043
:vartypmod 28
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 9
:restype 23
:restypmod -1
:resname trn_employr
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 9
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 9
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 9
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 10
:restype 23
:restypmod -1
:resname trn_prof
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 10
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 10
:vartype 23
:vartypmod -1
:varlevelsup 0
: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
:varlevelsup 0
:varnoold 1
:varoattno 11
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 12
:restype 1043
:restypmod 7
:resname trn_tooth
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 12
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 12
:vartype 1043
:vartypmod 7
:varlevelsup 0
:varnoold 1
:varoattno 12
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 13
:restype 1043
:restypmod 10
:resname trn_surface
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 13
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 13
:vartype 1043
:vartypmod 10
:varlevelsup 0
:varnoold 1
:varoattno 13
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 14
:restype 1042
:restypmod 5
:resname trn_flag
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 14
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 14
:vartype 1042
:vartypmod 5
:varlevelsup 0
:varnoold 1
:varoattno 14
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 15
:restype 23
:restypmod -1
:resname trn_counter
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 15
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 15
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 15
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 16
:restype 23
:restypmod -1
:resname trn_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
:resname trn_lab
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 17
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 17
:vartype 1042
:vartypmod 5
:varlevelsup 0
:varnoold 1
:varoattno 17
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 18
:restype 1082
:restypmod -1
:resname trn_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
:resname trn_hist_flag
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 19
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 19
:vartype 1042
:vartypmod 5
:varlevelsup 0
:varnoold 1
:varoattno 19
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 20
:restype 23
:restypmod -1
:resname trn_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
:resname trn_commcode
:ressortgroupref 0
:resorigtbl 789839
:resorigcol 21
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 21
:vartype 1043
:vartypmod 7
:varlevelsup 0
: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
: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
:varnoold 1
:varoattno 18
}
{CONST
:consttype 1082
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ -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
:varnoold 1
:varoattno 2
}
{CONST
:consttype 23
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 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
:constvalue 4 [ 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
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname trn_patno
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 2
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 1042
:restypmod 5
:resname trn_bill_inc
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 3
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 3
:vartype 1042
:vartypmod 5
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 1043
:restypmod 13
:resname trn_userid
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 4
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 4
:vartype 1043
:vartypmod 13
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname trn_location
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 5
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 1082
:restypmod -1
:resname trn_date
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 6
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 6
:vartype 1082
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 7
:restype 23
:restypmod -1
:resname trn_sercode
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 7
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 7
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 7
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 8
:restype 1043
:restypmod 28
:resname trn_descr
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 8
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 8
:vartype 1043
:vartypmod 28
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 9
:restype 23
:restypmod -1
:resname trn_employer
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 9
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 9
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 9
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 10
:restype 23
:restypmod -1
:resname trn_prof
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 10
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 10
:vartype 23
:vartypmod -1
:varlevelsup 0
: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
:resname trn_tooth
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 12
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 12
:vartype 1043
:vartypmod 7
:varlevelsup 0
: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
:varlevelsup 0
:varnoold 1
:varoattno 13
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 14
:restype 1042
:restypmod 5
:resname trn_flag
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 14
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 14
:vartype 1042
:vartypmod 5
:varlevelsup 0
:varnoold 1
:varoattno 14
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 15
:restype 23
:restypmod -1
:resname trn_counter
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 15
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 15
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 15
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 16
:restype 23
:restypmod -1
:resname trn_guarantr
:ressortgroupref 0
:resorigtbl 2487466
: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
:resname trn_lab
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 17
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 17
:vartype 1042
:vartypmod 5
:varlevelsup 0
:varnoold 1
:varoattno 17
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 18
:restype 1082
:restypmod -1
:resname trn_old_date
:ressortgroupref 0
:resorigtbl 2487466
: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
:resname trn_hist_flag
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 19
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 19
:vartype 1042
:vartypmod 5
:varlevelsup 0
:varnoold 1
:varoattno 19
}
}
{TARGETENTRY
:resdom
{RESDOM
:resno 20
:restype 23
:restypmod -1
:resname trn_check_no
:ressortgroupref 0
:resorigtbl 2487466
: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
:resname trn_commcode
:ressortgroupref 0
:resorigtbl 2487466
:resorigcol 21
:resjunk false
}
:expr
{VAR
:varno 1
:varattno 21
:vartype 1043
:vartypmod 7
:varlevelsup 0
:varnoold 1
:varoattno 21
}
}
)
:qual (
{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
:constvalue 4 [ 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
:constvalue 4 [ 91 -8 -1 -1 ]
}
)
}
{OPEXPR
:opno 1096
:opfuncid 1088
: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
:constvalue 4 [ -96 6 0 0 ]
}
)
}
)
)
:indxqualorig ((
{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
: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
:varnoold 1
:varoattno 18
}
{CONST
:consttype 1082
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ -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 the two
systems - 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.
David Price wrote: > 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: EXPLAIN ANALYZE is usually considered enough > 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: > 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) These queries are different. The first returns 687 rows and the second 713 rows. You need to check your systems if they are supposed to be identical. Things to check: 1. postgresql.conf settings match - different costs could cause this 2. statistics on the two columns (trn_patno,trn_old_date) - if they differ considerably between systems that would also explain it. I suspect the second one, at a wild guess the working system happens to know 199999 is fairly rare wheras the second just estimates an average. If the stats don't help, people are going to want to see the entire query+plan. Could you repost with the query and explain analyse on both system. Oh, and some idea on how many rows/unique values are involved in the important columns. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> Things to check:
> 1. postgresql.conf settings match - different costs could cause this
> 2. statistics on the two columns (trn_patno,trn_old_date) - if they
> differ considerably between systems that would also explain it.
The different estimated row counts could only come from #2. I suspect
David has forgotten to run ANALYZE on the second system.
I agree that EXPLAIN VERBOSE output is not helpful...
regards, tom lane
On Wed, 25 Aug 2004, Richard Huxton wrote: > > 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) > > > 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) > > These queries are different. The first returns 687 rows and the second > 713 rows. The 687 and 713 are the number of rows in the plan, not the number of rows the queries return. -- /Dennis Björklund
Dennis Bjorklund wrote: > On Wed, 25 Aug 2004, Richard Huxton wrote: >> >>These queries are different. The first returns 687 rows and the second >>713 rows. > > > The 687 and 713 are the number of rows in the plan, not the number of rows > the queries return. D'OH! Thanks Dennis -- Richard Huxton Archonet Ltd
Tom, your suspicions were correct - ANALYZE was not being run.
I run vacuumdb via a cron script during off hours. After checking the
scripts on both systems, I found that on the system that was not functioning
correctly that the '-z' (analyze) command line option to vacuumdb was
missing. After correcting it and re-running the script, the poorly
performing SQL query takes only a few seconds as opposed to 15 minutes.
Thank you for your help!
- David
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Wednesday, August 25, 2004 3:08 PM
To: Richard Huxton
Cc: David Price; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Optimizer Selecting Incorrect Index
Richard Huxton <dev@archonet.com> writes:
> Things to check:
> 1. postgresql.conf settings match - different costs could cause this
> 2. statistics on the two columns (trn_patno,trn_old_date) - if they
> differ considerably between systems that would also explain it.
The different estimated row counts could only come from #2. I suspect
David has forgotten to run ANALYZE on the second system.
I agree that EXPLAIN VERBOSE output is not helpful...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly