Re: Some Improvement - Mailing list pgsql-hackers

Tom Lane wrote:
> 
> Tim Perdue <tperdue@valinux.com> writes:
> > I added the suggested index and changed my sql and the subjective tests
> > seem to be improved somewhat. I checked EXPLAIN and it is using the new
> > index.
> 
> > I still think there must be sorting going on, as the result is returned
> > instantly if you remove the ORDER BY.
> 
> You "think"?  What does EXPLAIN show in the two cases?
> 
>                         regards, tom lane

Following is the info - again thanks for your help. If you need, I can
try to re-install 6.5.3 and re-import the database. Although with tables
of this size, it is a true nightmare to do this. If you feel the info is
valuable, I'd like to help.

Tim


With the ORDER BY


db_geocrawler=# explain verbose SELECT fld_mailid, fld_mail_date,
fld_mail_is_followup, fld_mail_from, fld_mail_subject FROM
tbl_mail_archive WHERE fld_mail_list='35' AND fld_mail_date between
'20000100' AND '20000199' ORDER BY fld_mail_date DESC LIMIT 51 OFFSET 0;
NOTICE:  QUERY DUMP:

{ SORT :startup_cost 5.03 :total_cost 5.03 :rows 1 :width 44 :state <>
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname fld_mailid :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY
:resdom { RESDOM :resno 2 :restype 1042 :restypmod 18 :resname
fld_mail_date :reskey 1 :reskeyop 1051 :ressortgroupref 1 :resjunk false
} :expr { VAR :varno 1 :varattno 3 :vartype 1042 :vartypmod 18 
:varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY :resdom { RESDOM
:resno 3 :restype 23 :restypmod -1 :resname fld_mail_is_followup :reskey
0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 4 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 25
:restypmod -1 :resname fld_mail_from :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 5
:vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 5}} {
TARGETENTRY :resdom { RESDOM :resno 5 :restype 25 :restypmod -1 :resname
fld_mail_subject :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false
} :expr { VAR :varno 1 :varattno 6 :vartype 25 :vartypmod -1 
:varlevelsup 0 :varnoold 1 :varoattno 6}}) :qpqual <> :lefttree {
INDEXSCAN :startup_cost 0.00 :total_cost 5.02 :rows 1 :width 44 :state
<> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname fld_mailid :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY
:resdom { RESDOM :resno 2 :restype 1042 :restypmod 18 :resname
fld_mail_date :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false }
:expr { VAR :varno 1 :varattno 3 :vartype 1042 :vartypmod 18 
:varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY :resdom { RESDOM
:resno 3 :restype 23 :restypmod -1 :resname fld_mail_is_followup :reskey
0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 4 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 25
:restypmod -1 :resname fld_mail_from :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 5
:vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 5}} {
TARGETENTRY :resdom { RESDOM :resno 5 :restype 25 :restypmod -1 :resname
fld_mail_subject :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false
} :expr { VAR :varno 1 :varattno 6 :vartype 25 :vartypmod -1 
:varlevelsup 0 :varnoold 1 :varoattno 6}}) :qpqual <> :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1
:indxid ( 5913536) :indxqual (({ EXPR :typeOid 16  :opType op :oper {
OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 2} { CONST :consttype 23 :constlen 4 :constisnull false
:constvalue  4 [ 35 0 0 0 ]  :constbyval true })} { EXPR :typeOid 16 
:opType op :oper { OPER :opno 1061 :opid 1052 :opresulttype 16 } :args
({ VAR :varno 1 :varattno 2 :vartype 1042 :vartypmod 18  :varlevelsup 0
:varnoold 1 :varoattno 3} { CONST :consttype 1042 :constlen -1
:constisnull false :constvalue  12 [ 12 0 0 0 50 48 48 48 48 49 48 48 ] 
:constbyval false })} { EXPR :typeOid 16  :opType op :oper { OPER :opno
1059 :opid 1050 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 2
:vartype 1042 :vartypmod 18  :varlevelsup 0 :varnoold 1 :varoattno 3} {
CONST :consttype 1042 :constlen -1 :constisnull false :constvalue  12 [
12 0 0 0 50 48 48 48 48 49 57 57 ]  :constbyval false })}))
:indxqualorig (({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96
:opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 2 :vartype
23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 2} { CONST
:consttype 23 :constlen 4 :constisnull false :constvalue  4 [ 35 0 0 0
]  :constbyval true })} { EXPR :typeOid 16  :opType op :oper { OPER
:opno 1061 :opid 1052 :opresulttype 16 } :args ({ VAR :varno 1 :varattno
3 :vartype 1042 :vartypmod 18  :varlevelsup 0 :varnoold 1 :varoattno 3}
{ CONST :consttype 1042 :constlen -1 :constisnull false :constvalue  12
[ 12 0 0 0 50 48 48 48 48 49 48 48 ]  :constbyval false })} { EXPR
:typeOid 16  :opType op :oper { OPER :opno 1059 :opid 1050 :opresulttype
16 } :args ({ VAR :varno 1 :varattno 3 :vartype 1042 :vartypmod 18 
:varlevelsup 0 :varnoold 1 :varoattno 3} { CONST :consttype 1042
:constlen -1 :constisnull false :constvalue  12 [ 12 0 0 0 50 48 48 48
48 49 57 57 ]  :constbyval false })})) :indxorderdir 1 } :righttree <>
:extprm () :locprm () :initplan <> :nprm 0  :nonameid 0 :keycount 1 }
NOTICE:  QUERY PLAN:

Sort  (cost=5.03..5.03 rows=1 width=44) ->  Index Scan using idx_archive_list_date on tbl_mail_archive 
(cost=0.00..5.02 rows=1 width=44)

EXPLAIN
db_geocrawler=#


Without the ORDER BY



db_geocrawler=#
db_geocrawler=# explain verbose SELECT fld_mailid, fld_mail_date,
fld_mail_is_followup, fld_mail_from, fld_mail_subject FROM
tbl_mail_archive WHERE fld_mail_list='35' AND fld_mail_date between
'20000100' AND '20000199' LIMIT 51 OFFSET 0;
NOTICE:  QUERY DUMP:

{ INDEXSCAN :startup_cost 0.00 :total_cost 5.02 :rows 1 :width 44 :state
<> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname fld_mailid :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY
:resdom { RESDOM :resno 2 :restype 1042 :restypmod 18 :resname
fld_mail_date :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 1 :varattno 3 :vartype 1042 :vartypmod 18 
:varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY :resdom { RESDOM
:resno 3 :restype 23 :restypmod -1 :resname fld_mail_is_followup :reskey
0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 4 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 25
:restypmod -1 :resname fld_mail_from :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 5
:vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 5}} {
TARGETENTRY :resdom { RESDOM :resno 5 :restype 25 :restypmod -1 :resname
fld_mail_subject :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false
} :expr { VAR :varno 1 :varattno 6 :vartype 25 :vartypmod -1 
:varlevelsup 0 :varnoold 1 :varoattno 6}}) :qpqual <> :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1
:indxid ( 5913536) :indxqual (({ EXPR :typeOid 16  :opType op :oper {
OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 2} { CONST :consttype 23 :constlen 4 :constisnull false
:constvalue  4 [ 35 0 0 0 ]  :constbyval true })} { EXPR :typeOid 16
:opType op :oper { OPER :opno 1061 :opid 1052 :opresulttype 16 } :args
({ VAR :varno 1 :varattno 2 :vartype 1042 :vartypmod 18  :varlevelsup 0
:varnoold 1 :varoattno 3} { CONST :consttype 1042 :constlen -1
:constisnull false :constvalue  12 [ 12 0 0 0 50 48 48 48 48 49 48 48 ] 
:constbyval false })} { EXPR :typeOid 16  :opType op :oper { OPER :opno
1059 :opid 1050 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 2
:vartype 1042 :vartypmod 18  :varlevelsup 0 :varnoold 1 :varoattno 3} {
CONST :consttype 1042 :constlen -1 :constisnull false :constvalue  12 [
12 0 0 0 50 48 48 48 48 49 57 57 ]  :constbyval false })}))
:indxqualorig (({ EXPR :typeOid 16  :opType op :oper { OPER :opno 96
:opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 2 :vartype
23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 2} { CONST
:consttype 23 :constlen 4 :constisnull false :constvalue  4 [ 35 0 0 0
]  :constbyval true })} { EXPR :typeOid 16  :opType op :oper { OPER
:opno 1061 :opid 1052 :opresulttype 16 } :args ({ VAR :varno 1 :varattno
3 :vartype 1042 :vartypmod 18  :varlevelsup 0 :varnoold 1 :varoattno 3}
{ CONST :consttype 1042 :constlen -1 :constisnull false :constvalue  12
[ 12 0 0 0 50 48 48 48 48 49 48 48 ]  :constbyval false })} { EXPR
:typeOid 16  :opType op :oper { OPER :opno 1059 :opid 1050 :opresulttype
16 } :args ({ VAR :varno 1 :varattno 3 :vartype 1042 :vartypmod 18 
:varlevelsup 0 :varnoold 1 :varoattno 3} { CONST :consttype 1042
:constlen -1 :constisnull false :constvalue  12 [ 12 0 0 0 50 48 48 48
48 49 57 57 ]  :constbyval false })})) :indxorderdir 1 }
NOTICE:  QUERY PLAN:

Index Scan using idx_archive_list_date on tbl_mail_archive 
(cost=0.00..5.02 rows=1 width=44)

EXPLAIN



-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723