Optimizer Selecting Incorrect Index

From: "David Price" <dprice(at)dentfirst(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Optimizer Selecting Incorrect Index
Date: 2004-08-25 15:07:27
Message-ID: OJEEJPPOBPODJFPEOFAGEEBMCGAA.dprice@dentfirst.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2004-08-25 15:47:19 Re: Optimizer Selecting Incorrect Index
Previous Message Stef 2004-08-25 14:30:41 Re: Query kills machine.