problem in select

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

See the FAQ, queries using IN (<subselect>) aren't implemented optimally,
a conversion to an EXISTS form may help.

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 of EXPLAIN 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

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
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

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 of EXPLAIN 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 ircalend
Tabl
e "ircal
e
nd"
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)
gedis30=# \d ircalend_3
Table "ircalend_3"
Column | Type | Modifiers
---------------+--------------------------+------------
mtipo | character varying(1) | default ''
id | integer |
nr_icod | character varying(15) | default ''
crev | character varying(2) | default ''
idcalend | integer |
idcalend1 | integer |
mese | integer |
gtipo | integer |
caattesa
| i
n
teger |
rapporto | integer |
oreatnorm | integer |
oreatstr | integer |
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 | 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 |
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 |
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_lav | 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_a
tt_,
0
A ircalend_3_ca_att_pre_1_ca_att_,
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)
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 wi
d
th=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=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 verb
ose sel
e
ct titolo,id,anno from ircalend where anno=2002
and id in(select distinct(idcalend) from ircalend_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 :
=0
A
roattno 3}}) :qpqual <> :lefttree { SEQSCAN :startup_cost 0.00 :total_cost 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 :varnoold 1 :varoattno 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 { IN
DEXSCAN
:
startup_
st 0.00 :total_cost 6586.80 :rows 71112 :width 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" "rapporto" "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 true :checkForRead 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 <>})}) :s
ubselect

<>}} :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 (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: http://www.email.it/f

Sponsor:
Nessun'idea per un regalo? Da noi troverai novità preziose ogni giorno.
Clicca qui: http://adv2.email.it/cgi-bin/foclick.cgi?mid=773&amp;d=16-9

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
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
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)
gedis30=# \d ircalend_3
Table "ircalend_3"
Column | Type | Modifiers
---------------+--------------------------+------------
mtipo | character varying(1) | default ''
id | integer |
nr_icod | character varying(15) | default ''
crev | character varying(2) | default ''
idcalend | integer |
idcalend1 | integer |
mese | integer |
gtipo | integer |
caattesa | integer |
rapporto | integer |
oreatn
orm
|
integer |
oreatstr | integer |
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 | 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 |
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 |
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_lav | 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_,
ircalend_3_caasslav,

ir
c
alend_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)
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)
EXPLAIN

gedis30=# explain ANALYZE select titolo,id,anno fro
m ircale
n
d 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=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(idcal
end) fro
m
ircalend_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_cost 473
.1
5 :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 :varnoold 1 :varoattno 3} {=0
D
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 71112 :width 4 :qptargetlist ({ TARGET
ENTRY
0Aesdom { 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" "oreatnorm" "oreatstr
"oreini01" "orelav1p" "oreini02" "orelav2p" "oreini03" "orelav3p"
"caasslav" "inizfm" "orasslav" "orevarnorm" "orevarstr" "
ca_att_p
r
e_
"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 :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 <> :npr
m 0 :sc
a
nrelid 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 (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: http://www.email.it/f

Sponsor:
Non diventare matto per lei: regalale un gioiello di Gioie.it.
Clicca qui: http://adv2.email.it/cgi-bin/foclick.cgi?mid=771&amp;d=16-9

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/