Обсуждение: subselects doesn't work in v7.0.3

jose (jose@sferacarta.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)

verde:/var/lib/postgreSQL$ time psql interno < ./sql

Cancel request sent
ERROR:  Query was cancelled.
ERROR:  Query was cancelled.

real    954m44.582s
user    0m0.020s
sys     0m0.000s


- I tried the same query on DBMaker and it takes only about 1 minute.

dmSQL>
USE db #1 disconnected from db:<interno> by user:<sysadm>

real    1m10.070s
user    0m0.020s
sys     0m0.020s

- I tried the same query in a table with few rows and it
seems to work.



Sample Code


No file was uploaded with this report

pgsql-bugs@postgresql.org wrote:
>
> jose (jose@sferacarta.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.

Cheers,
                    Andrew.
--
_____________________________________________________________________
           Andrew McMillan, e-mail: Andrew@catalyst.net.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

Andrew McMillan wrote:

> pgsql-bugs@postgresql.org wrote:
> >
> > jose (jose@sferacarta.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@catalyst.net.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