Re: Postgres performance comments from a MySQL user - Mailing list pgsql-general

At 02:04 17.06.2003, Tom Lane said:
--------------------[snip]--------------------
>It's really hard to believe that you could see that kind of ratio from
>any sort of cache effects, even kernel disk buffer cache which is
>normally pretty large.  Are you sure you were getting the same plan each
>time?  I'd like to see EXPLAIN ANALYZE output from all three cases.
--------------------[snip]--------------------

I have EXPLAIN ANALYZE VERBOSE available. I noticed that even _removing_
the ilike clause from the query, thus using _only_ index columns, did NOT
change this behaviour.

test=# explain analyze verbose
test-# select distinct t1.owid
test-# from rv2_mdata t1
test-# where t1.dcid='ADDR' and t1.dsid='DICT' and t1.drid=110 and t1.usg &
16 = 16
test-# and t1.nxid = 0
test-# and t1.cst ilike '%redist%'
test-# and t1.owid > 10
test-# ;

NOTICE:  QUERY DUMP:

{ UNIQUE :startup_cost 3.84 :total_cost 3.84 :rows 1 :width 4 :qptargetlist
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname
owid :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR
:varno 1 :varattno 3 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 3}}) :qpqual <> :lefttree { SORT :startup_cost 3.84 :total_cost
3.84 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno
1 :restype 23 :restypmod -1 :resname owid :reskey 1 :reskeyop 97
:ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 3
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 3}})
:qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 3.83 :rows
1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
23 :restypmod -1 :resname owid :reskey 0 :reskeyop 0 :ressortgroupref 1
:resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1
 :varlevelsup 0 :varnoold 1 :varoattno 3}}) :qpqual ({ EXPR :typeOid 16
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ EXPR
:typeOid 23  :opType op :oper { OPER :opno 1880 :opid 1898 :opresulttype 23
} :args ({ VAR :varno 1 :varattno 8 :vartype 23 :vartypmod -1  :varlevelsup
0 :varnoold 1 :varoattno 8} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue  4 [ 16 0 0 0 ] })} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue  4 [ 16 0 0
0 ] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 1631 :opid 1633
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 16 :vartype 1043
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 16} { CONST :consttype
25 :constlen -1 :constbyval false :constisnull false :constvalue  12 [ 12 0
0 0 37 114 101 100 105 115 116 37 ] })} { EXPR :typeOid 16  :opType op
:oper { OPER :opno 521 :opid 147 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 3 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 3} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue  4 [ 10 0 0 0 ] })}) :lefttree <> :righttree
<> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1 :indxid (
22424505) :indxqual (({ EXPR :typeOid 16  :opType op :oper { OPER :opno
1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1
:vartype 1043 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 5} {
CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false
:constvalue  8 [ 8 0 0 0 65 68 68 82 ] })} { EXPR :typeOid 16  :opType op
:oper { OPER :opno 1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 2 :vartype 1043 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 6} { CONST :consttype 1043 :constlen -1 :constbyval false
:constisnull false :constvalue  8 [ 8 0 0 0 68 73 67 84 ] })} { EXPR
:typeOid 16  :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 }
:args ({ VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 1 :varoattno 7} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue  4 [ 110 0 0 0 ] })} { EXPR :typeOid 16
 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR
:varno 1 :varattno 4 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 11} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue  4 [ 0 0 0 0 ] })})) :indxqualorig (({ EXPR
:typeOid 16  :opType op :oper { OPER :opno 1062 :opid 1070 :opresulttype 16
} :args ({ VAR :varno 1 :varattno 5 :vartype 1043 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 5} { CONST :consttype 1043 :constlen
-1 :constbyval false :constisnull false :constvalue  8 [ 8 0 0 0 65 68 68
82 ] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 1062 :opid 1070
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 6 :vartype 1043
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 6} { CONST :consttype
1043 :constlen -1 :constbyval false :constisnull false :constvalue  8 [ 8 0
0 0 68 73 67 84 ] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96
:opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 7 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue  4 [ 110 0 0
0 ] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 11 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 11} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue  4 [ 0 0 0 0
] })})) :indxorderdir 1 } :righttree <> :extprm () :locprm () :initplan <>
:nprm 0  :keycount 1 } :righttree <> :extprm () :locprm () :initplan <>
:nprm 0  :numCols 1 :uniqColIdx 1 }
psql:x:8: NOTICE:  QUERY PLAN:

Unique  (cost=3.84..3.84 rows=1 width=4) (actual time=3155.24..3155.27
rows=11 loops=1)
  ->  Sort  (cost=3.84..3.84 rows=1 width=4) (actual time=3155.24..3155.25
rows=11 loops=1)
        ->  Index Scan using id_mdata_dictid_string on rv2_mdata t1
(cost=0.00..3.83 rows=1 width=4) (actual time=210.96..3155.00 rows=11 loops=1)
Total runtime: 3155.42 msec

EXPLAIN

test=# explain analyze verbose
test-# select distinct t1.owid
test-# from rv2_mdata t1
test-# where t1.dcid='ADDR' and t1.dsid='DICT' and t1.drid=110 and t1.usg &
16 = 16
test-# and t1.nxid = 0
test-# and t1.cst ilike '%redist%'
test-# and t1.owid > 10
test-# ;

NOTICE:  QUERY DUMP:

{ UNIQUE :startup_cost 3.84 :total_cost 3.84 :rows 1 :width 4 :qptargetlist
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname
owid :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR
:varno 1 :varattno 3 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 3}}) :qpqual <> :lefttree { SORT :startup_cost 3.84 :total_cost
3.84 :rows 1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno
1 :restype 23 :restypmod -1 :resname owid :reskey 1 :reskeyop 97
:ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 3
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 3}})
:qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 3.83 :rows
1 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
23 :restypmod -1 :resname owid :reskey 0 :reskeyop 0 :ressortgroupref 1
:resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1
 :varlevelsup 0 :varnoold 1 :varoattno 3}}) :qpqual ({ EXPR :typeOid 16
:opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ EXPR
:typeOid 23  :opType op :oper { OPER :opno 1880 :opid 1898 :opresulttype 23
} :args ({ VAR :varno 1 :varattno 8 :vartype 23 :vartypmod -1  :varlevelsup
0 :varnoold 1 :varoattno 8} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue  4 [ 16 0 0 0 ] })} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue  4 [ 16 0 0
0 ] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 1631 :opid 1633
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 16 :vartype 1043
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 16} { CONST :consttype
25 :constlen -1 :constbyval false :constisnull false :constvalue  12 [ 12 0
0 0 37 114 101 100 105 115 116 37 ] })} { EXPR :typeOid 16  :opType op
:oper { OPER :opno 521 :opid 147 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 3 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 3} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue  4 [ 10 0 0 0 ] })}) :lefttree <> :righttree
<> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1 :indxid (
22424505) :indxqual (({ EXPR :typeOid 16  :opType op :oper { OPER :opno
1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1
:vartype 1043 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 5} {
CONST :consttype 1043 :constlen -1 :constbyval false :constisnull false
:constvalue  8 [ 8 0 0 0 65 68 68 82 ] })} { EXPR :typeOid 16  :opType op
:oper { OPER :opno 1062 :opid 1070 :opresulttype 16 } :args ({ VAR :varno 1
:varattno 2 :vartype 1043 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 6} { CONST :consttype 1043 :constlen -1 :constbyval false
:constisnull false :constvalue  8 [ 8 0 0 0 68 73 67 84 ] })} { EXPR
:typeOid 16  :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 }
:args ({ VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold 1 :varoattno 7} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull false :constvalue  4 [ 110 0 0 0 ] })} { EXPR :typeOid 16
 :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR
:varno 1 :varattno 4 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 11} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue  4 [ 0 0 0 0 ] })})) :indxqualorig (({ EXPR
:typeOid 16  :opType op :oper { OPER :opno 1062 :opid 1070 :opresulttype 16
} :args ({ VAR :varno 1 :varattno 5 :vartype 1043 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 5} { CONST :consttype 1043 :constlen
-1 :constbyval false :constisnull false :constvalue  8 [ 8 0 0 0 65 68 68
82 ] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 1062 :opid 1070
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 6 :vartype 1043
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 6} { CONST :consttype
1043 :constlen -1 :constbyval false :constisnull false :constvalue  8 [ 8 0
0 0 68 73 67 84 ] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96
:opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 7 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue  4 [ 110 0 0
0 ] })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 11 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 11} { CONST :consttype
23 :constlen 4 :constbyval true :constisnull false :constvalue  4 [ 0 0 0 0
] })})) :indxorderdir 1 } :righttree <> :extprm () :locprm () :initplan <>
:nprm 0  :keycount 1 } :righttree <> :extprm () :locprm () :initplan <>
:nprm 0  :numCols 1 :uniqColIdx 1 }
psql:x:8: NOTICE:  QUERY PLAN:

Unique  (cost=3.84..3.84 rows=1 width=4) (actual time=10.70..10.73 rows=11
loops=1)
  ->  Sort  (cost=3.84..3.84 rows=1 width=4) (actual time=10.69..10.70
rows=11 loops=1)
        ->  Index Scan using id_mdata_dictid_string on rv2_mdata t1
(cost=0.00..3.83 rows=1 width=4) (actual time=0.60..10.62 rows=11 loops=1)
Total runtime: 10.85 msec


--
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/