query slow problem

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