Re: subselects doesn't work in v7.0.3

Andrew McMillan wrote:

> pgsql-bugs(at)postgresql(dot)org wrote:
> >
> > jose (jose(at)sferacarta(dot)com) reports a bug with a severity of 2
> > The lower the number the more severe it is.
> >
> > Short Description
> > subselects doesn't work in v7.0.3
> >
> > Long Description
> > Version: PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2
> >
> > - I'm trying the following query in a table with 1973093 rows:
> >
> > EXPLAIN select count(*)
> > from marche
> > where ristampa = 'S'
> > and marca in
> > (
> > select marca from marche where ristampa is null and
> > data_lotto between '1998/07/01' and '1999/01/31'
> > );
> >
> > NOTICE: QUERY PLAN:
> > Aggregate (cost=98854229180.08..98854229180.08 rows=1 width=4)
> > -> Seq Scan on marche (cost=0.00..98854229130.75 rows=19731 width=4)
> > SubPlan
> > -> Materialize (cost=50101.13..50101.13 rows=6577 width=12)
> > -> Seq Scan on marche (cost=0.00..50101.13 rows=6577 width=12)
> > EXPLAIN
> >
> > - but it takes to many time: (after about 16 hours I interrupt the query)
>
> This is a known bug with IN ( ... ) and the use of indexes - you would
> get better results using EXISTS.

Yes. EXISTS works.
PostgreSQL takes 9.720 secs against DBMaker 7.145 secs
Thank you very much
Jose'

>
>
> Cheers,
> Andrew.
> --
> _____________________________________________________________________
> Andrew McMillan, e-mail: Andrew(at)catalyst(dot)net(dot)nz
> Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
> Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267