Postgres Pro
Downloads
Home   >   mailing lists

Re: Slow query needs a kick in the pants. - Mailing list pgsql-general

From Arjen van der Meijden
Subject Re: Slow query needs a kick in the pants.
Date
Msg-id 002f01c2f4be$8fd798b0$3ac15e91@acm
Whole thread Raw
In response to Slow query needs a kick in the pants.  ("Dann Corbit" <DCorbit@connx.com>)
List pgsql-general
Tree view
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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] Namens Dann Corbit
Verzonden: vrijdag 28 maart 2003 0:30
Aan: pgsql-general@postgresql.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.

pgsql-general by date:

Previous
From: "Miguel Angel Martin"
Date:
Subject: Re: ¿What's wrong?
Next
From: Martijn van Oosterhout
Date:
Subject: Re: About OIDs