pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
reforming query for 7.0.2
3+ messages / 2 participants
[nested] [flat]

* reforming query for 7.0.2
@ 2002-01-16 13:28 Markus Bertheau <twanger@bluetwanger.de>
  2002-01-16 16:27 ` Re: reforming query for 7.0.2 Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Markus Bertheau @ 2002-01-16 13:28 UTC (permalink / raw)
  To: pgsql-sql

Hello,

how do I reform this query to work with 7.0.2?


select * from personen join (select count(personen_id), personen_id from
orders group by personen_id) as ordertemp on personen.personen_id =
ordertemp.personen_id

If the table structure is needed I will happily post it here. Although I
don't think it is.

TIA

Markus Bertheau

Attachments:

  signature.asc (application/pgp-signature, 231B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: reforming query for 7.0.2
  2002-01-16 13:28 reforming query for 7.0.2 Markus Bertheau <twanger@bluetwanger.de>
@ 2002-01-16 16:27 ` Josh Berkus <josh@agliodbs.com>
  2002-01-17 12:56   ` Re: reforming query for 7.0.2 Markus Bertheau <twanger@bluetwanger.de>
  0 siblings, 1 reply; 3+ messages in thread

From: Josh Berkus @ 2002-01-16 16:27 UTC (permalink / raw)
  To: Markus Bertheau <twanger@bluetwanger.de>; pgsql-sql

Markus,

> how do I reform this query to work with 7.0.2?

Better question:  Why are you working with 7.0.2?  Even the mass-market Linux
 distros (like Red Hat and SuSE) now come with 7.1.x.

> select * from personen join (select count(personen_id), personen_id from
> orders group by personen_id) as ordertemp on personen.personen_id =
> ordertemp.personen_id

Acutally, this query needs some reforming on its own.  While it will work, the
 following version will achieve the same result, much faster, in 7.1 (and 7.2,
 for that matter):

SELECT personen.field1, personen.field2, personen.field3, count(order_id) as
 no_orders
FROM personen LEFT OUTER JOIN orders ON personen.personen_id =
 orders.personen_id
GROUP BY  personen.field1, personen.field2, personen.field3

... you see, your subselect above is completely unnecessary.  And slower than a
 LEFT OUTER JOIN.  Simplicity, simplicity, simplicity!

Now, we have the problem of no LEFT JOIN support in 7.0, so:

SELECT personen.field1, personen.field2, personen.field3, count(order_id) as
 no_orders
FROM personen JOIN orders ON personen.personen_id = orders.personen_id
GROUP BY  personen.field1, personen.field2, personen.field3
UNION
SELECT personen.field1, personen.field2, personen.field3, 0 as no_orders
FROM personen
WHERE NOT EXISTS (SELECT personen_id FROM orders WHERE personen_id =
 personen.personen_id);

Share & Enjoy!  

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: reforming query for 7.0.2
  2002-01-16 13:28 reforming query for 7.0.2 Markus Bertheau <twanger@bluetwanger.de>
  2002-01-16 16:27 ` Re: reforming query for 7.0.2 Josh Berkus <josh@agliodbs.com>
@ 2002-01-17 12:56   ` Markus Bertheau <twanger@bluetwanger.de>
  0 siblings, 0 replies; 3+ messages in thread

From: Markus Bertheau @ 2002-01-17 12:56 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

Josh,

> Markus,
> 
> > how do I reform this query to work with 7.0.2?
> 
> Better question:  Why are you working with 7.0.2?  Even the mass-market Linux
>  distros (like Red Hat and SuSE) now come with 7.1.x.

Yeah, I know... An upgrade is definetely in the queue. But it will not
happen before 7.2 (if my imagination of its release date is correct), so
we will most probably go to that. 
> 
> > select * from personen join (select count(personen_id), personen_id from
> > orders group by personen_id) as ordertemp on personen.personen_id =
> > ordertemp.personen_id
> 
> Acutally, this query needs some reforming on its own.  While it will work, the
>  following version will achieve the same result, much faster, in 7.1 (and 7.2,
>  for that matter):
> 
> SELECT personen.field1, personen.field2, personen.field3, count(order_id) as
>  no_orders
> FROM personen LEFT OUTER JOIN orders ON personen.personen_id =
>  orders.personen_id
> GROUP BY  personen.field1, personen.field2, personen.field3
> 
> ... you see, your subselect above is completely unnecessary.  And slower than a
>  LEFT OUTER JOIN.  Simplicity, simplicity, simplicity!

Thank you for that. 

> Now, we have the problem of no LEFT JOIN support in 7.0, so:
> 
> SELECT personen.field1, personen.field2, personen.field3, count(order_id) as
>  no_orders
> FROM personen JOIN orders ON personen.personen_id = orders.personen_id
> GROUP BY  personen.field1, personen.field2, personen.field3
> UNION
> SELECT personen.field1, personen.field2, personen.field3, 0 as no_orders
> FROM personen
> WHERE NOT EXISTS (SELECT personen_id FROM orders WHERE personen_id =
>  personen.personen_id);

Well, I actually don't need the left outer join, because I don't want
persons included that have no orders. I think the original query didn't
include them. So I turn the left outer join into a join and have the
same query on both versions :-). (We use 7.1.3 on our development
server).

Thanks again.

But let's take a look at explain. The old query shows a much smaller
cost estimate compared to the new one. Both databases are vacuumed on a
regular (daily) basis. First 7.1.3:

cenes_test=> select version();
                           version                           
-------------------------------------------------------------
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

// old query
cenes_test=> explain select * from personen join (select
count(orders_id), personen_id from orders group by personen_id) as
ordertemp on ordertemp.personen_id = personen.personen_id;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=11.76..42.32 rows=15 width=272)
  ->  Subquery Scan ordertemp  (cost=11.76..12.50 rows=15 width=8)
        ->  Aggregate  (cost=11.76..12.50 rows=15 width=8)
              ->  Group  (cost=11.76..12.13 rows=147 width=8)
                    ->  Sort  (cost=11.76..11.76 rows=147 width=8)
                          ->  Seq Scan on orders  (cost=0.00..6.47
rows=147 width=8)
  ->  Index Scan using personen_pkey on personen  (cost=0.00..2.02
rows=1 width=264)

EXPLAIN

// new query

cenes_test=> explain select personen.nachname, personen.vorname,
personen.firma, personen.personen_id, count(orders_id) from personen
join orders on personen.personen_id = orders.personen_id group by
personen.nachname, personen.vorname, personen.firma,
personen.personen_id;
NOTICE:  QUERY PLAN:

Aggregate  (cost=162.53..164.37 rows=15 width=48)
  ->  Group  (cost=162.53..164.00 rows=147 width=48)
        ->  Sort  (cost=162.53..162.53 rows=147 width=48)
              ->  Merge Join  (cost=139.59..157.24 rows=147 width=48)
                    ->  Sort  (cost=127.83..127.83 rows=1265 width=40)
                          ->  Seq Scan on personen  (cost=0.00..62.65
rows=1265 width=40)
                    ->  Sort  (cost=11.76..11.76 rows=147 width=8)
                          ->  Seq Scan on orders  (cost=0.00..6.47
rows=147 width=8)

EXPLAIN

// That's a lot more. About row estimates: 

cenes_test=> select count(personen_id) from personen;
 count 
-------
  1272
(1 row)

cenes_test=> select count(orders_id) from orders;
 count 
-------
   189
(1 row)

cenes_test=> select count(orders_id) from orders group by personen_id
order by count desc;
 count 
-------
    41
    33
    15
    12
    10
     8
     7
     5
     4
     3
     2
     2
     2
     2
     2
     2
     2
     1
// 36 x 1 snipped
(54 rows)

On the production db:
cenes=> select version();
                            version                            
---------------------------------------------------------------
 PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)

// old query doesn't work
// new query
cenes=> explain select personen.nachname, personen.vorname,
personen.firma, personen.personen_id, count(orders_id) from personen
join orders on personen.personen_id = orders.personen_id group by
personen.nachname, personen.vorname, personen.firma,
personen.personen_id;
NOTICE:  QUERY PLAN:

Aggregate  (cost=201.91..204.87 rows=24 width=48)
  ->  Group  (cost=201.91..204.28 rows=237 width=48)
        ->  Sort  (cost=201.91..201.91 rows=237 width=48)
              ->  Merge Join  (cost=170.85..192.56 rows=237 width=48)
                    ->  Sort  (cost=18.72..18.72 rows=237 width=8)
                          ->  Seq Scan on orders  (cost=0.00..9.37
rows=237 width=8)
                    ->  Sort  (cost=152.13..152.13 rows=1500 width=40)
                          ->  Seq Scan on personen  (cost=0.00..73.00
rows=1500 width=40)

EXPLAIN

cenes=> select count(personen_id) from personen;
 count 
-------
  1501
(1 row)

cenes=> select count(orders_id) from orders;
 count 
-------
   238
(1 row)

 count 
-------
    34
    16
    12
    12
     8
     7
     6
     5
     4
     2
     2
     2
     2
     2
     2
     2
     2
     2
     2
     2
     2
     2
     2
     1
// the rest is 1
(129 rows)

How's that?

Markus Bertheau

Attachments:

  signature.asc (application/pgp-signature, 231B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-01-17 12:56 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-16 13:28 reforming query for 7.0.2 Markus Bertheau <twanger@bluetwanger.de>
2002-01-16 16:27 ` Josh Berkus <josh@agliodbs.com>
2002-01-17 12:56   ` Markus Bertheau <twanger@bluetwanger.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox