Re: problem in select - Mailing list pgsql-general
| From | frank_lupo |
|---|---|
| Subject | Re: problem in select |
| Date | |
| Msg-id | H2IQTF$DE21328E7A3DE6CF36839DE32A2B1437@email.it Whole thread Raw |
| Responses |
Re: problem in select
(Martijn van Oosterhout <kleptog@svana.org>)
|
| List | pgsql-general |
> 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
pgsql-general by date: