Обсуждение: problem in select

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 ?

----
Prendi GRATIS l'email universale che... risparmia: clicca qui

Sponsor:
Se avete deciso di andare in vacanza solo adesso non preoccupatevi da oliviero.it trovate zaini e valigie da riempire con tutto quello che più amate portarvi in vacanza.
Clicca qui

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