Re: problem in select - Mailing list pgsql-general
| From | frank_lupo |
|---|---|
| Subject | Re: problem in select |
| Date | |
| Msg-id | H2IQXO$CF91BF3FC6797BE86611E569BD30F144@email.it Whole thread Raw |
| List | pgsql-general |
> On Fri, 13 Sep 2002, [utf-8] frank_lupo wrote:
>
> > This selection is more fast in ingres vs postgres
> > Ingres 6.4 0.04 sec
> > Postgres 7.2 0.42 sec
> > select titolo,id,anno from ircalend where anno=2002 and id in(select distinct(idcalend) from ircalend_3) order by anno,titolo
> > because ?
>
> What happens if you do:
>
> SELECT titolo,id.anno
> FROM ircalend
> WHERE < DIV>> anno = 2002
> AND
> EXISTS (SELECT 1 FROM ircalend_3 WHERE idcalend = id)
> ORDER BY anno, titolo
>
>
> Try doing an EXPLAIN ANALYZE on your query as well. That will give you the
> execution plan.
>
>
> --
> Nigel J. Andrews
>
>
This is a definitions of table and EXPLAIN SELECT
gedis30=# \d ircalend
&n bsp;&nbs p ; Table "ircalend"
Column | Type | Modifiers
--------+-----------------------+------------
id | integer |
titolo | character varying(80) | default ''
anno | smallint |
Indexes: ircalend_id
&n bsp;&nbs p ; Table "ircalend"
Column | Type | Modifiers
--------+-----------------------+------------
id | integer |
titolo | character varying(80) | default ''
anno | smallint |
Indexes: ircalend_id
gedis30=# select count(*) from ircalend\g
count
-------
7
(1 row)
count
-------
7
(1 row)
gedis30=# \d ircalend_3
Table "ircalend_3"
Column | Ty pe & nbsp; | Modifiers
---------------+--------------------------+------------
mtipo | character varying(1) | default ''
id | integer |
nr_icod | character varying(15) | default ''
crev | character varying(2) | default ''
idcalend | integer |
idcalend1 | integer &nbs p; |
& nbsp;mese | integer |
gtipo | integer |
caattesa | integer |
rapporto | integer |
oreatnorm | integer |
oreatstr | integer & nbsp; |< B R> oreini01 | timestamp with time zone |
orelav1p | integer |
oreini02 | timestamp with time zone |
orelav2p | integer |
oreini03 | timestamp with time zone |
orelav3p | integer |
caasslav | integer |
inizfm | timestamp with time zone |
orasslav &n bsp;&nbs p ; | integer |
orevarnorm | integer |
orevarstr | integer |
ca_att_pre_1 | integer |
pre_ore_1_att | integer |
ca_att_pre_2 | integer |
dic_ore_3_att | integer &nbs p; |
& nbsp;ca_att_dic_4 | integer |
dic_ore_4_att | integer |
ca_att_dic_5 | integer |
dic_ore_5_att | integer |
mensa | character varying(25) | default ''
stato | integer |
pre_ore_2_att | integer |
& nbsp;ca_ a tt_pre_3 | integer |
pre_ore_3_att | integer |
ca_att_pre_4 | integer |
pre_ore_4_att | integer |
ca_att_pre_5 | integer |
pre_ore_5_att | integer |
dic_iniz_lav | timestamp with time zone |
dic_fine_lav | timestamp with time zone |
dic_ore_lav | integer &nbs p; & nbsp; |
ca_att_dic_1 | integer |
dic_ore_1_att | integer |
ca_att_dic_2 | integer |
dic_ore_2_att | integer |
ca_att_dic_3 | integer |
Indexes: ircalend_3_ca_att_dic_1_ca_att_,
ircalend_3_ca_att_pre_1_ca_att_,
ircalend_3_caasslav, & nbsp; ircalend_3_caattesa,
ircalend_3_id,
ircalend_3_idcalend,
ircalend_3_idcalend1,
ircalend_3_mese,
ircalend_3_nr_icod_crev
Table "ircalend_3"
Column | Ty pe & nbsp; | Modifiers
---------------+--------------------------+------------
mtipo | character varying(1) | default ''
id | integer |
nr_icod | character varying(15) | default ''
crev | character varying(2) | default ''
idcalend | integer |
idcalend1 | integer &nbs p; |
& nbsp;mese | integer |
gtipo | integer |
caattesa | integer |
rapporto | integer |
oreatnorm | integer |
oreatstr | integer & nbsp; |< B R> oreini01 | timestamp with time zone |
orelav1p | integer |
oreini02 | timestamp with time zone |
orelav2p | integer |
oreini03 | timestamp with time zone |
orelav3p | integer |
caasslav | integer |
inizfm | timestamp with time zone |
orasslav &n bsp;&nbs p ; | integer |
orevarnorm | integer |
orevarstr | integer |
ca_att_pre_1 | integer |
pre_ore_1_att | integer |
ca_att_pre_2 | integer |
dic_ore_3_att | integer &nbs p; |
& nbsp;ca_att_dic_4 | integer |
dic_ore_4_att | integer |
ca_att_dic_5 | integer |
dic_ore_5_att | integer |
mensa | character varying(25) | default ''
stato | integer |
pre_ore_2_att | integer |
& nbsp;ca_ a tt_pre_3 | integer |
pre_ore_3_att | integer |
ca_att_pre_4 | integer |
pre_ore_4_att | integer |
ca_att_pre_5 | integer |
pre_ore_5_att | integer |
dic_iniz_lav | timestamp with time zone |
dic_fine_lav | timestamp with time zone |
dic_ore_lav | integer &nbs p; & nbsp; |
ca_att_dic_1 | integer |
dic_ore_1_att | integer |
ca_att_dic_2 | integer |
dic_ore_2_att | integer |
ca_att_dic_3 | integer |
Indexes: ircalend_3_ca_att_dic_1_ca_att_,
ircalend_3_ca_att_pre_1_ca_att_,
ircalend_3_caasslav, & nbsp; ircalend_3_caattesa,
ircalend_3_id,
ircalend_3_idcalend,
ircalend_3_idcalend1,
ircalend_3_mese,
ircalend_3_nr_icod_crev
gedis30=# select count(*) from ircalend_3\g
count
-------
71372
(1 row)
count
-------
71372
(1 row)
gedis30=# explain select titolo,id,anno from ircalend where anno=2002 and id in(select distinct(idcalend) from ircalend_3) order by anno,titolo;
NOTICE: QUERY PLAN:
NOTICE: QUERY PLAN:
Sort (cost=47353.16..47353.16 rows=1 width=49)
-> Seq Scan on ircalend (cost=0.00..47353.15 rows=1 width=49)
SubPlan
; & n bsp; -> Materialize (cost=6764.58..6764.58 rows=7111 width=4)
-> Unique (cost=0.00..6764.58 rows=7111 width=4)
-> Index Scan using ircalend_3_idcalend on ircalend_3 (cost=0.00..6586.80 rows=71112 width=4)
-> Seq Scan on ircalend (cost=0.00..47353.15 rows=1 width=49)
SubPlan
; & n bsp; -> Materialize (cost=6764.58..6764.58 rows=7111 width=4)
-> Unique (cost=0.00..6764.58 rows=7111 width=4)
-> Index Scan using ircalend_3_idcalend on ircalend_3 (cost=0.00..6586.80 rows=71112 width=4)
EXPLAIN
gedis30=# explain ANALYZE select titolo,id,anno from ircalend where anno=2002 and id in(select distinct(idcalend) from ircalend_3) order by anno,titolo;
NOTICE: QUERY PLAN:
Sort (cost=47353.16..47353.16 rows=1 width=49) (actual time=824.65..824.66 rows=1 loops=1)
-> Seq Scan on ircalend (cost=0.00..47353.15 rows=1 width=49) (actual time=823.11..823.12 rows=1 loops=3 D 1)
SubPlan
-> Materialize (cost=6764.58..6764.58 rows=7111 width=4) (actual time=823.00..823.01 rows=4 loops=1)
-> Unique (cost=0.00..6764.58 rows=7111 width=4) (actual time=0.16..822.98 rows=5 loops=1)
-> Index Scan using ircalend_3_idcalend on ircalend_3 (cost=0.00..6586.80 rows=71112 width=4) (actual time=0.15..615.65 rows=71372 loops=1)
Total runtime: 824.93 msec
-> Seq Scan on ircalend (cost=0.00..47353.15 rows=1 width=49) (actual time=823.11..823.12 rows=1 loops=3 D 1)
SubPlan
-> Materialize (cost=6764.58..6764.58 rows=7111 width=4) (actual time=823.00..823.01 rows=4 loops=1)
-> Unique (cost=0.00..6764.58 rows=7111 width=4) (actual time=0.16..822.98 rows=5 loops=1)
-> Index Scan using ircalend_3_idcalend on ircalend_3 (cost=0.00..6586.80 rows=71112 width=4) (actual time=0.15..615.65 rows=71372 loops=1)
Total runtime: 824.93 msec
EXPLAIN
gedis30=# explain verbose select titolo,id,anno from ircalend where anno=2002
and id in(select distinct(idcalend) from ircalend_3) order by anno,titolo ;
NOT I CE: QUERY DUMP:
{ SORT :startup_cost 47353.16 :total_cost 47353.16 :rows 1 :width 49 :qptarget
st ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 84 :resna
titolo :reskey 2 :reskeyop 1066 :ressortgroupref 2 :resjunk false } :expr { V
:varno 1 :varattno 2 :vartype 1043 :vartypmod 84 :varlevelsup 0 :varnoold 1
aroattno 2}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1
resname id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { V
:varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :v
oattno 1}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 21 :restypmod -1 :
sname anno :reskey 1 :reskeyop 95 :ressortgroupref 1 :resjunk false } :expr {
R :varno 1 :varattno 3 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :
roattno 3}}) :qpqual <> :lefttree { SEQSCAN :startup_cost 0.00 :total_cost 473
.15 :rows 1 :wid th 49 :q p targetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :
stype 1043 :restypmod 84 :resname titolo :reskey 0 :reskeyop 0 :ressortgroupre
2 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 8
:varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY :resdom { RESDOM :res
2 :restype 23 :restypmod -1 :resname id :reskey 0 :reskeyop 0 :ressortgroupre
0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno
:restype 21 :restypmod -1 :resname anno :reskey 0 :reskeyop 0 :ressortgroupre
1 :resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 21 :vartypmod -1
varlevelsup 0 :varnoold 1 :varoattno 3}}) :qpqual ({ EXPR :typeOid 16 :opType
p :oper { OPER :opno 532 :opid 158 :opresulttype 16 } :args ({ VAR :varno 1 :v
attno 3 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3} {
NST :cons ttype 23 :constlen 4 :constbyval true :constisnull false :constvalue
[ -46 7 0 0 ] })} { EXPR :typeOid 16 :opType subp :oper { SUBPLAN :plan { MA
RIAL :startup_cost 6764.58 :total_cost 6764.58 :rows 7111 :width 4 :qptargetli
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname i
alend :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR :v
no 1 :varattno 5 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoat
o 5}}) :qpqual <> :lefttree { UNIQUE :startup_cost 0.00 :total_cost 6764.58 :r
s 7111 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restyp
23 :restypmod -1 :resname idcalend :reskey 0 :reskeyop 0 :ressortgroupref 1 :r
junk false } :expr { VAR :varno 1 :varattno 5 :vartype 23 :vartypmod -1 :varl
elsup 0 :varnoold 1 :varoattno 5}}) :qpqual <> :lefttree { INDEXSCAN :startup_
st 0.00 :total_cost 6586.80 :rows 71112 :width 4 :qptargetlist ({ TARG ETENTRY< B R>esdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname idcalend :reskey 0
eskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 5
vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 5}}) :qpqual <
:lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanre
d 1 :indxid ( 5553382) :indxqual (<>) :indxqualorig (<>) :indxorderdir 1 } :ri
ttree <> :extprm () :locprm () :initplan <> :nprm 0 :numCols 1 :uniqColIdx 1
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 } :planid 1 :rtable (
RTE :relname ircalend_3 :relid 5422386 :subquery <> :alias <> :eref { ATTR :r
name ircalend_3 :attrs ( "mtipo" "id" "nr_icod" "crev" "idcalend" "i
alend1" "mese" "gtipo" "caattesa" "rapporto" "oreatno rm" ; "oreatstr
"oreini01" "orelav1p" "oreini02" "orelav2p" "oreini03" "orelav3p"
"caasslav" "inizfm" "orasslav" "orevarnorm" "orevarstr" "ca_att_pre_
"pre_ore_1_att" "ca_att_pre_2" "dic_ore_3_att" "ca_att_dic_4" "dic_
e_4_att" "ca_att_dic_5" "dic_ore_5_att" "mensa" "stato" "pre_ore_2_a
" "ca_att_pre_3" "pre_ore_3_att" "ca_att_pre_4" "pre_ore_4_att" "ca_
t_pre_5" "pre_ore_5_att" "dic_iniz_lav" "dic_fine_lav" "dic_ore_lav"
ca_att_dic_1" "dic_ore_1_att" "ca_att_dic_2" "dic_ore_2_att" "ca_att_d
_3" )} :inh false :inFromCl true :checkForRead true :checkForWri te false :chec
sUser 0}) :setprm () :parprm () :slink { SUBLINK :subLinkType 2 :useor false :
fthand <> :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 :con
byval true :constisnull true :constvalue <>})}) :subselect <>}} :args <>}) :le
tree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :keycount 2 }
NOTICE: QUERY PLAN:
st ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1043 :restypmod 84 :resna
titolo :reskey 2 :reskeyop 1066 :ressortgroupref 2 :resjunk false } :expr { V
:varno 1 :varattno 2 :vartype 1043 :vartypmod 84 :varlevelsup 0 :varnoold 1
aroattno 2}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1
resname id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { V
:varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :v
oattno 1}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 21 :restypmod -1 :
sname anno :reskey 1 :reskeyop 95 :ressortgroupref 1 :resjunk false } :expr {
R :varno 1 :varattno 3 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :
roattno 3}}) :qpqual <> :lefttree { SEQSCAN :startup_cost 0.00 :total_cost 473
.15 :rows 1 :wid th 49 :q p targetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :
stype 1043 :restypmod 84 :resname titolo :reskey 0 :reskeyop 0 :ressortgroupre
2 :resjunk false } :expr { VAR :varno 1 :varattno 2 :vartype 1043 :vartypmod 8
:varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY :resdom { RESDOM :res
2 :restype 23 :restypmod -1 :resname id :reskey 0 :reskeyop 0 :ressortgroupre
0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1
varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno
:restype 21 :restypmod -1 :resname anno :reskey 0 :reskeyop 0 :ressortgroupre
1 :resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 21 :vartypmod -1
varlevelsup 0 :varnoold 1 :varoattno 3}}) :qpqual ({ EXPR :typeOid 16 :opType
p :oper { OPER :opno 532 :opid 158 :opresulttype 16 } :args ({ VAR :varno 1 :v
attno 3 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3} {
NST :cons ttype 23 :constlen 4 :constbyval true :constisnull false :constvalue
[ -46 7 0 0 ] })} { EXPR :typeOid 16 :opType subp :oper { SUBPLAN :plan { MA
RIAL :startup_cost 6764.58 :total_cost 6764.58 :rows 7111 :width 4 :qptargetli
({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname i
alend :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR :v
no 1 :varattno 5 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoat
o 5}}) :qpqual <> :lefttree { UNIQUE :startup_cost 0.00 :total_cost 6764.58 :r
s 7111 :width 4 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restyp
23 :restypmod -1 :resname idcalend :reskey 0 :reskeyop 0 :ressortgroupref 1 :r
junk false } :expr { VAR :varno 1 :varattno 5 :vartype 23 :vartypmod -1 :varl
elsup 0 :varnoold 1 :varoattno 5}}) :qpqual <> :lefttree { INDEXSCAN :startup_
st 0.00 :total_cost 6586.80 :rows 71112 :width 4 :qptargetlist ({ TARG ETENTRY< B R>esdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname idcalend :reskey 0
eskeyop 0 :ressortgroupref 1 :resjunk false } :expr { VAR :varno 1 :varattno 5
vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 5}}) :qpqual <
:lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanre
d 1 :indxid ( 5553382) :indxqual (<>) :indxqualorig (<>) :indxorderdir 1 } :ri
ttree <> :extprm () :locprm () :initplan <> :nprm 0 :numCols 1 :uniqColIdx 1
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 } :planid 1 :rtable (
RTE :relname ircalend_3 :relid 5422386 :subquery <> :alias <> :eref { ATTR :r
name ircalend_3 :attrs ( "mtipo" "id" "nr_icod" "crev" "idcalend" "i
alend1" "mese" "gtipo" "caattesa" "rapporto" "oreatno rm" ; "oreatstr
"oreini01" "orelav1p" "oreini02" "orelav2p" "oreini03" "orelav3p"
"caasslav" "inizfm" "orasslav" "orevarnorm" "orevarstr" "ca_att_pre_
"pre_ore_1_att" "ca_att_pre_2" "dic_ore_3_att" "ca_att_dic_4" "dic_
e_4_att" "ca_att_dic_5" "dic_ore_5_att" "mensa" "stato" "pre_ore_2_a
" "ca_att_pre_3" "pre_ore_3_att" "ca_att_pre_4" "pre_ore_4_att" "ca_
t_pre_5" "pre_ore_5_att" "dic_iniz_lav" "dic_fine_lav" "dic_ore_lav"
ca_att_dic_1" "dic_ore_1_att" "ca_att_dic_2" "dic_ore_2_att" "ca_att_d
_3" )} :inh false :inFromCl true :checkForRead true :checkForWri te false :chec
sUser 0}) :setprm () :parprm () :slink { SUBLINK :subLinkType 2 :useor false :
fthand <> :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 :con
byval true :constisnull true :constvalue <>})}) :subselect <>}} :args <>}) :le
tree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1
:righttree <> :extprm () :locprm () :initplan <> :nprm 0 :keycount 2 }
NOTICE: QUERY PLAN:
Sort (cost=47353.16..47353.16 rows=1 width=49)
-> Seq Scan on ircalend (cost=0.00..47353.15 rows=1 width=49)
SubPlan
-> Materialize (cos t=6764 . 58..6764.58 rows=7111 width=4)
-> Unique (cost=0.00..6764.58 rows=7111 width=4)
-> Index Scan using ircalend_3_idcalend on ircalend_3
ost=0.00..6586.80 rows=71112 width=4)
-> Seq Scan on ircalend (cost=0.00..47353.15 rows=1 width=49)
SubPlan
-> Materialize (cos t=6764 . 58..6764.58 rows=7111 width=4)
-> Unique (cost=0.00..6764.58 rows=7111 width=4)
-> Index Scan using ircalend_3_idcalend on ircalend_3
ost=0.00..6586.80 rows=71112 width=4)
EXPLAIN
Tanks
----
Prendi GRATIS l'email universale che... risparmia: clicca qui
Sponsor:
Non diventare matto per lei: regalale un gioiello di Gioie.it.
Clicca qui
pgsql-general by date: