query slow problem - Mailing list pgsql-general

select id,de2 from irtab where id in (select distinct(ruolofunz) from
irelbtes_1 where entpian=118331)\g

this query is more fast in
velocity:
ingres 6.4     .48 sec
postgres 7.2  1.34 sec

help me

--------------------------

gedis30=# explain select id,de2 from irtab where id in (select distinct
(ruolofunz) from irelbtes_1 where entpian=118331)\g
NOTICE:  QUERY PLAN:

Seq Scan on irtab  (cost=0.00..442623660.25 rows=80332 width=150)
  SubPlan
    ->  Materialize  (cost=2754.88..2754.88 rows=76 width=4)
          ->  Unique  (cost=2752.99..2754.88 rows=76 width=4)
                ->  Sort  (cost=2752.99..2752.99 rows=755 width=4)
                      ->  Index Scan using irelbtes_1_entpian on
irelbtes_1  (cost=0.00..2716.89 rows=755 width=4)

gedis30=# explain ANALYZE select id,de2 from irtab where id in (select
distinct(ruolofunz) from irelbtes_1 where entpian=118331)\g
NOTICE:  QUERY PLAN:

Seq Scan on irtab  (cost=0.00..442623660.25 rows=80332 width=150)
(actual time=1
094.14..19160.24 rows=8 loops=1)
  SubPlan
    ->  Materialize  (cost=2754.88..2754.88 rows=76 width=4) (actual
time=0.01..
0.05 rows=9 loops=160664)
          ->  Unique  (cost=2752.99..2754.88 rows=76 width=4) (actual
time=37.12
..49.40 rows=9 loops=1)
                ->  Sort  (cost=2752.99..2752.99 rows=755 width=4)
(actual time=
37.11..42.89 rows=2300 loops=1)
                      ->  Index Scan using irelbtes_1_entpian on
irelbtes_1  (co
st=0.00..2716.89 rows=755 width=4) (actual time=0.15..29.28 rows=2300
loops=1)
Total runtime: 19160.72 msec

gedis30=# explain verbose select id,de2 from irtab where id in (select
distinct(ruolofunz) from irelbtes_1 where entpian=118331)\g
NOTICE:  QUERY DUMP:

{ SEQSCAN :startup_cost 0.00 :total_cost 442623660.25 :rows
80332 :width 150 :qp
targetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
23 :restypmod -1 :r
esname id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR
:varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup
0 :varnoold 1 :varo
attno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype
1043 :restypmod 260 :
resname de2 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VA
R :varno 1 :varattno 5 :vartype 1043 :vartypmod 260  :varlevelsup
0 :varnoold 1
:varoattno 5}}) :qpqual ({ EXPR :typeOid 16  :opType subp :oper {
SUBPLAN :plan
{ MATERIAL :startup_cost 2754.88 :total_cost 2754.88 :rows 76 :width
4 :qptarget
list ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -
1 :resname
 ruolofunz :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk
false } :expr { VAR
 :varno 1 :varattno 9 :vartype 23 :vartypmod -1  :varlevelsup
0 :varnoold 1 :var
oattno 9}}) :qpqual <> :lefttree { UNIQUE :startup_cost
2752.99 :total_cost 2754
.88 :rows 76 :width 4 :qptargetlist ({ TARGETENTRY :resdom {
RESDOM :resno 1 :re
stype 23 :restypmod -1 :resname ruolofunz :reskey 0 :reskeyop
0 :ressortgroupref
 1 :resjunk false } :expr { VAR :varno 1 :varattno 9 :vartype
23 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 9}}) :qpqual <> :lefttree {
SORT :startup_
cost 2752.99 :total_cost 2752.99 :rows 755 :width 4 :qptargetlist ({
TARGETENTRY
 :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname
ruolofunz :reskey
1 :reskeyop 97 :ressortgroupref 1 :resjunk false } :expr { VAR :varno
1 :varattn
o 9 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno
9}}) :qpqua
l <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 2716.89 :rows
755 :wid
th 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
23 :restypm
od -1 :resname ruolofunz :reskey 0 :reskeyop 0 :ressortgroupref
1 :resjunk false
 } :expr { VAR :varno 1 :varattno 9 :vartype 23 :vartypmod -
1  :varlevelsup 0 :v
arnoold 1 :varoattno 9}}) :qpqual <> :lefttree <> :righttree <> :extprm
() :locp
rm () :initplan <> :nprm 0  :scanrelid 1 :indxid ( 1721547) :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 4} { CONST :consttype 23 :constlen 4 :constbyval
true :constisnull f
alse :constvalue  4 [ 59 -50 1 0 ] })})) :indxqualorig (({
EXPR :typeOid 16  :op
Type op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({
VAR :varno 1
:varattno 4 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold
1 :varoattno 4}
{ CONST :consttype 23 :constlen 4 :constbyval true :constisnull
false :constvalu
e  4 [ 59 -50 1 0 ] })})) :indxorderdir 1 } :righttree <> :extprm
() :locprm ()
:initplan <> :nprm 0  :keycount 1 } :righttree <> :extprm () :locprm
() :initpla
n <> :nprm 0  :numCols 1 :uniqColIdx 1 } :righttree <> :extprm
() :locprm () :in
itplan <> :nprm 0 } :planid 3 :rtable ({ RTE :relname irelbtes_1 :relid
445114
:subquery <> :alias <> :eref { ATTR :relname irelbtes_1 :attrs
( "id"   "idelbte
s"   "grtest"   "entpian"   "idpad"   "ruolo"   "stato"   "funzione"   "
ruolofun
z"   "entitaop"   "data_iniz_prev"   "data_fine_prev"   "oreprev"   "cos
toprev"
  "umcosto"   "oreass"   "costoass"   "data_distr"   "data_evas"   "oree
ff"   "c
ostoeff"   "mod_trasm"   "mod_risp"   "idelbase"   "flag"   "gruppo" )}
:inh fal
se :inFromCl true :checkForRead true :checkForWrite false :checkAsUser
0}) :setp
rm () :parprm () :slink { SUBLINK :subLinkType 2 :useor false :lefthand
<> :oper
 ({ 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 1} { CONST :consttype 23 :constlen 4 :constbyval
true :con
stisnull true :constvalue <>})}) :subselect <>}} :args <>}) :lefttree
<> :rightt
ree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1 }
NOTICE:  QUERY PLAN:

Seq Scan on irtab  (cost=0.00..442623660.25 rows=80332 width=150)
  SubPlan
    ->  Materialize  (cost=2754.88..2754.88 rows=76 width=4)
          ->  Unique  (cost=2752.99..2754.88 rows=76 width=4)
                ->  Sort  (cost=2752.99..2752.99 rows=755 width=4)
                      ->  Index Scan using irelbtes_1_entpian on
irelbtes_1  (cost=0.00..2716.89 rows=755 width=4)

EXPLAIN
gedis30=#



Bye !!
Frank Lupo (Wolf) !!



--
Prendi GRATIS l'email universale che... risparmia: http://www.email.it/f

Sponsor:
Non sai cosa regalare? Vai su Artefiori, il Portale del Verde!
clicca qui:
Clicca qui: http://adv2.email.it/cgi-bin/foclick.cgi?mid=548&d=29-7