Thread: Re: problem in select
> On Friday 13 Sep 2002 5:32 pm, 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 ?
>
> Who knows? If you want people to help with this, you'll need to give them more
> information. Things like the output o f E X PLAIN SELECT ... and some details on
> how many records are in each table would be a good start.
>
> So, given that we don't have that I'd suggest looking to see if you can't
> replace IN with EXISTS since PostgreSQL isn't very clever about IN. See mail
> archives and manual for details. But look at the EXPLAIN readout first.
>
> - Richard Huxton
>
This is a definitions of table and EXPLAIN SELECT
gedis30=# \ d ircale n d
Table "ircalend"
Column | Type | Modifiers
--------+-----------------------+------------
id | integer |
titolo | character varying(80) | default ''
anno | smallint |
Indexes: ircalend_id
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 | ; & n bsp; Type | Modifiers
---------------+--------------------------+------------
mtipo | character varying(1) | default ''
id | integer |
nr_icod | character varying(15) | default ''
crev | character varying(2) | default ''
idcalend | integer |
idcalend1 | integer &n b sp; |
mese | integer |
gtipo | integer |
caattesa | integer |
rapporto | integer |
oreatnorm | integer |
oreatstr | integer &nb sp; ; |
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 tim e zone | < BR> orasslav | 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 &n b sp; |
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 &nb sp; ; |
ca_att_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_l av & nbsp; | integer |
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_,
& nbsp;&nb s p; ircalend_3_caasslav,
ircalend_3_caattesa,
ircalend_3_id,
ircalend_3_idcalend,
ircalend_3_idcalend1,
ircalend_3_mese,
ircalend_3_nr_icod_crev
Table "ircalend_3"
Column | ; & n bsp; Type | Modifiers
---------------+--------------------------+------------
mtipo | character varying(1) | default ''
id | integer |
nr_icod | character varying(15) | default ''
crev | character varying(2) | default ''
idcalend | integer |
idcalend1 | integer &n b sp; |
mese | integer |
gtipo | integer |
caattesa | integer |
rapporto | integer |
oreatnorm | integer |
oreatstr | integer &nb sp; ; |
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 tim e zone | < BR> orasslav | 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 &n b sp; |
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 &nb sp; ; |
ca_att_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_l av & nbsp; | integer |
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_,
& nbsp;&nb s p; ircalend_3_caasslav,
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
& nbsp;&nb s p; -> 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
& nbsp;&nb s p; -> 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) (act ual time 3D823.11..823.12 rows=1 loops=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) (act ual time 3D823.11..823.12 rows=1 loops=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) fro m ircale n d_3) order by anno,titolo;
NOTICE: 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_co s t 473
.15 :rows 1 :width 49 :qptargetlist ({ 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 :varno old 1 :v a roattno 3} {
NST :consttype 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 71 112 :wid t h 4 :qptargetlist ({ TARGETENTRY
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" &nbs p; "rapp o rto" "oreatnorm" "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 tr ue :chec k ForRead true :checkForWrite 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_co s t 473
.15 :rows 1 :width 49 :qptargetlist ({ 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 :varno old 1 :v a roattno 3} {
NST :consttype 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 71 112 :wid t h 4 :qptargetlist ({ TARGETENTRY
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" &nbs p; "rapp o rto" "oreatnorm" "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 tr ue :chec k ForRead true :checkForWrite 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
->&nb s p; 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
ost=0.00..6586.80 rows=71112 width=4)
-> Seq Scan on ircalend (cost=0.00..47353.15 rows=1 width=49)
SubPlan
->&nb s p; 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
ost=0.00..6586.80 rows=71112 width=4)
EXPLAIN
Tanks
Bye !! Frank Lupo (Wolf) !! ----
Prendi GRATIS l'email universale che... risparmia: clicca qui
Sponsor:
Nessun'idea per un regalo? Da noi troverai novità preziose ogni giorno.
Clicca qui
Well, here's your problem. Using both IN (which is slow) and DISTINCT (which is completely unnessesary). How does this differ from: select titolo,id,anno from ircalend where anno=2002 and exists (select 1 from ircalend_3 where idcalen=ircalend.id) order by anno,titolo; On Mon, Sep 16, 2002 at 08:46:27AM +0200, frank_lupo wrote: > 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: > 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 (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) -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.