Re: Slow query needs a kick in the pants.

Can't you do something with a query like:
SELECT a.oid
FROM CNX_DS2_1_BCHFIL_FILE a, CNX_DS2_1_BCHFIL_FILE b
WHERE a.FILE_KEY = b.FILE_KEY and ... and b.OID = NULL

?

I'm absolutely not sure whether this will give the expected results. But
my version is probably much faster.

Regards,

Arjen

-----Oorspronkelijk bericht-----
Van: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] Namens Dann Corbit
Verzonden: vrijdag 28 maart 2003 0:30
Aan: pgsql-general(at)postgresql(dot)org
Onderwerp: [GENERAL] Slow query needs a kick in the pants.

This query:

connxdatasync=# select "a".OID
connxdatasync-# from "CNX_DS2_1_BCHFIL_FILE" "a"
connxdatasync-# left outer join "CNX_DS_1_BCHFIL_FILE" "b" on
connxdatasync-# ( "a"."FILE_KEY" = "b"."FILE_KEY" and
connxdatasync(# "a"."SYS_YYYYMMDD" =
"b"."SYS_YYYYMMDD" and
connxdatasync(# "a"."SYS_HHMMSSUU" =
"b"."SYS_HHMMSSUU" )
connxdatasync-# where ( "b".OID is NULL )
connxdatasync-# ;

Is abysmally slow.

connxdatasync=# \d "CNX_DS2_1_BCHFIL_FILE"
Table "CNX_DS2_1_BCHFIL_FILE"
Attribute | Type | Modifier
--------------+---------------+----------
FILE_KEY | character(30) |
SYS_YYYYMMDD | character(8) |
SYS_HHMMSSUU | character(8) |
CRC | bigint | not null
Index: UA4IYKF5LY9402

connxdatasync=# explain VERBOSE
connxdatasync-# select "a".OID
connxdatasync-# from "CNX_DS2_1_BCHFIL_FILE" "a"
connxdatasync-# left outer join "CNX_DS_1_BCHFIL_FILE" "b" on
connxdatasync-# ( "a"."FILE_KEY" = "b"."FILE_KEY" and
connxdatasync(# "a"."SYS_YYYYMMDD" =
"b"."SYS_YYYYMMDD" and
connxdatasync(# "a"."SYS_HHMMSSUU" =
"b"."SYS_HHMMSSUU" )
connxdatasync-# where ( "b".OID is NULL )
connxdatasync-# ;
NOTICE: QUERY DUMP:

{ MERGEJOIN :startup_cost 312848.17 :total_cost 351988.93 :rows 973970
:width 80 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1
:restype 26 :restyp
mod -1 :resname oid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 65001 :varattno 1 :vartype 26 :vartypmod -1
:varlevelsup
0 :varnoold 1 :varoattno -2}}) :qpqual ({ EXPR :typeOid 16 :opType func
:oper { FUNC :funcid 1029 :functype 16 } :args ({ VAR :varno 65000
:varattno 4 :va
rtype 26 :vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno -2})})
:lefttree { SORT :startup_cost 155655.15 :total_cost 155655.15 :rows
973970 :width 40
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 26
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :exp
r { VAR :varno 1 :varattno -2 :vartype 26 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno -2}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype 1042 :
restypmod 34 :resname <> :reskey 3 :reskeyop 1049 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 1042
:vartypmod 34 :varlev
elsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno
3 :restype 1042 :restypmod 12 :resname <> :reskey 2 :reskeyop 1049
:ressortgroupre
f 0 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1042
:vartypmod 12 :varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY
:resdom { RESDOM
:resno 4 :restype 1042 :restypmod 12 :resname <> :reskey 1 :reskeyop
1049 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno
3 :vartype 10
42 :vartypmod 12 :varlevelsup 0 :varnoold 1 :varoattno 3}}) :qpqual <>
:lefttree { SEQSCAN :startup_cost 0.00 :total_cost 22726.70 :rows 973970
:width 40
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 26
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :exp
r { VAR :varno 1 :varattno -2 :vartype 26 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno -2}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype 1042 :
restypmod 34 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 1042
:vartypmod 34 :varlevels
up 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 3
:restype 1042 :restypmod 12 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :r
esjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1042 :vartypmod
12 :varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY :resdom {
RESDOM :resn
o 4 :restype 1042 :restypmod 12 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 3
:vartype 1042 :varty
pmod 12 :varlevelsup 0 :varnoold 1 :varoattno 3}}) :qpqual <> :lefttree
<> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid
1 } :right
tree <> :extprm () :locprm () :initplan <> :nprm 0 :keycount 3 }
:righttree { SORT :startup_cost 157193.02 :total_cost 157193.02 :rows
983068 :width 40 :q
ptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1042
:restypmod 34 :resname <> :reskey 3 :reskeyop 1049 :ressortgroupref 0
:resjunk false } :
expr { VAR :varno 2 :varattno 1 :vartype 1042 :vartypmod 34
:varlevelsup 0 :varnoold 2 :varoattno 1}} { TARGETENTRY :resdom { RESDOM
:resno 2 :restype 104
2 :restypmod 12 :resname <> :reskey 2 :reskeyop 1049 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 2 :varattno 2 :vartype 1042
:vartypmod 12 :var
levelsup 0 :varnoold 2 :varoattno 2}} { TARGETENTRY :resdom { RESDOM
:resno 3 :restype 1042 :restypmod 12 :resname <> :reskey 1 :reskeyop
1049 :ressortgrou
pref 0 :resjunk false } :expr { VAR :varno 2 :varattno 3 :vartype 1042
:vartypmod 12 :varlevelsup 0 :varnoold 2 :varoattno 3}} { TARGETENTRY
:resdom { RES
DOM :resno 4 :restype 26 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno -2
:vartype 26
:vartypmod -1 :varlevelsup 0 :varnoold 2 :varoattno -2}}) :qpqual <>
:lefttree { SEQSCAN :startup_cost 0.00 :total_cost 22938.68 :rows 983068
:width 40 :q
ptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1042
:restypmod 34 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :exp
r { VAR :varno 2 :varattno 1 :vartype 1042 :vartypmod 34 :varlevelsup 0
:varnoold 2 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2
:restype 1042 :
restypmod 12 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 2 :varattno 2 :vartype 1042
:vartypmod 12 :varlevels
up 0 :varnoold 2 :varoattno 2}} { TARGETENTRY :resdom { RESDOM :resno 3
:restype 1042 :restypmod 12 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :r
esjunk false } :expr { VAR :varno 2 :varattno 3 :vartype 1042 :vartypmod
12 :varlevelsup 0 :varnoold 2 :varoattno 3}} { TARGETENTRY :resdom {
RESDOM :resn
o 4 :restype 26 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno -2
:vartype 26 :vartypmo
d -1 :varlevelsup 0 :varnoold 2 :varoattno -2}}) :qpqual <> :lefttree
<> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid
2 } :righttr
ee <> :extprm () :locprm () :initplan <> :nprm 0 :keycount 3 } :extprm
() :locprm () :initplan <> :nprm 0 :jointype 1 :joinqual <>
:mergeclauses ({ EXPR
:typeOid 16 :opType op :oper { OPER :opno 1054 :opid 1048 :opresulttype
16 } :args ({ VAR :varno 65001 :varattno 4 :vartype 1042 :vartypmod 12
:varlevels
up 0 :varnoold 1 :varoattno 3} { VAR :varno 65000 :varattno 3 :vartype
1042 :vartypmod 12 :varlevelsup 0 :varnoold 2 :varoattno 3})} { EXPR
:typeOid 16 :
opType op :oper { OPER :opno 1054 :opid 1048 :opresulttype 16 } :args ({
VAR :varno 65001 :varattno 3 :vartype 1042 :vartypmod 12 :varlevelsup 0
:varnoold
1 :varoattno 2} { VAR :varno 65000 :varattno 2 :vartype 1042 :vartypmod
12 :varlevelsup 0 :varnoold 2 :varoattno 2})} { EXPR :typeOid 16
:opType op :ope
r { OPER :opno 1054 :opid 1048 :opresulttype 16 } :args ({ VAR :varno
65001 :varattno 2 :vartype 1042 :vartypmod 34 :varlevelsup 0 :varnoold
1 :varoattno
1} { VAR :varno 65000 :varattno 1 :vartype 1042 :vartypmod 34
:varlevelsup 0 :varnoold 2 :varoattno 1})})}
NOTICE: QUERY PLAN:

Merge Join (cost=312848.17..351988.93 rows=973970 width=80)
-> Sort (cost=155655.15..155655.15 rows=973970 width=40)
-> Seq Scan on CNX_DS2_1_BCHFIL_FILE a (cost=0.00..22726.70
rows=973970 width=40)
-> Sort (cost=157193.02..157193.02 rows=983068 width=40)
-> Seq Scan on CNX_DS_1_BCHFIL_FILE b (cost=0.00..22938.68
rows=983068 width=40)

Any ideas about how it might be possible to speed it up?
I can add indexes, tag columns or any other schema change that might
help.