Thread

1. subselects doesn't work in v7.0.3

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-01-04T10:29:04Z

   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
   
   
   

2. Re: subselects doesn't work in v7.0.3

   Andrew McMillan <andrew@catalyst.net.nz> — 2001-01-06T00:00:14Z

   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
   
   

3. Re: subselects doesn't work in v7.0.3

   jose <jose@sferacarta.com> — 2001-01-08T12:25:20Z

   
   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