Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id F23AB632240 for ; Wed, 13 Jan 2010 18:00:33 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51259-02 for ; Wed, 13 Jan 2010 22:00:23 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from bill.kwcorp.com (mailx.kwcorp.com [65.118.99.196]) by mail.postgresql.org (Postfix) with ESMTP id 3D76E632575 for ; Wed, 13 Jan 2010 18:00:22 -0400 (AST) X-TM-IMSS-Message-ID: <5d6bae1000000641@kwcorp.com> Received: from gw.homedecorators.com ([172.23.217.142]) by kwcorp.com ([192.168.85.25]) with ESMTP (TREND IMSS SMTP Service 7.0) id 5d6bae1000000641 ; Wed, 13 Jan 2010 16:00:16 -0600 Received: from HDC_MTA-MTA by gw.homedecorators.com with Novell_GroupWise; Wed, 13 Jan 2010 16:00:15 -0600 Message-Id: <4B4DEDB5.2E1C.0042.0@homedecorators.com> X-Mailer: Novell GroupWise Internet Agent 7.0.2 HP Date: Wed, 13 Jan 2010 15:58:45 -0600 From: "Jorge Montero" To: Subject: Hashaggregate estimates Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part2A002205.0__=" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.59 tagged_above=-10 required=5 tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/265 X-Sequence-Number: 37145 This is a MIME message. If you are reading this text, you may want to consider changing to a mail reader or gateway that understands how to properly handle MIME multipart messages. --=__Part2A002205.0__= Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable I'm having some performance problems in a few sales reports running on = postgres 8.3, running on Redhat 4.1.2. The hardware is a bit old, but it = performs well enough. The reports are the typical sales reporting fare: = Gather the sales of a time period based some criteria, aggregate them by = product, and then join with a bunch of other tables to display different = kinds of product information. =20 I'll spare you all the pain of looking at the entire queries: The ultimate = issue appears to be the same: The innermost table of the queries is an = inline view, which aggregates the data by product. It runs rather quickly, = but postgres underestimates the number of rows that come out of it, making = the rest of the query plan rather suboptimal. The inline view look like = this =20 select sku_id, sum(rs.price) as dollarsSold, sum(rs.quantity) as units =20 from reporting.sales rs=20 where rs.sale_date between ? AND ? group by sku_id =20 In some cases, we see extra conditions aside of the dates, but they have = the same shape. Barring a massive date range, the rest of the filters are = less selective than the date, so postgres uses an index on sale_date,sku_id= . I have increased the statistics calculations on sale_date quite a bit to = make sure Postgres makes decent row estimates.The problem is in the = aggregation: =20 "HashAggregate (cost=3D54545.20..54554.83 rows=3D642 width=3D24) (actual = time=3D87.945..98.219 rows=3D11462 loops=3D1)" " -> Index Scan using reporting_sales_sale_date_idx on sales rs = (cost=3D0.00..54288.63 rows=3D34209 width=3D24) (actual time=3D0.042..34.19= 4 rows=3D23744 loops=3D1)" " Index Cond: ((sale_date >=3D '2009-07-01 00:00:00'::timestamp = without time zone) AND (sale_date <=3D '2009-07-06 00:00:00'::timestamp = without time zone))" "Total runtime: 10.110 ms" =20 As you an seem the Index scan's estimate is pretty close when I use a = single condition, but the aggregate estimate is off by a factor of 20. = When I add further conditions, the estimate just gets worse and worse. =20 "HashAggregate (cost=3D8894.83..8894.85 rows=3D1 width=3D24) (actual = time=3D6.444..6.501 rows=3D92 loops=3D1)" " -> Index Scan using reporting_sales_sale_date_sku_id_idx on sales rs = (cost=3D0.00..8894.76 rows=3D9 width=3D24) (actual time=3D0.103..6.278 = rows=3D94 loops=3D1)" " Index Cond: ((sale_date >=3D '2009-07-01 00:00:00'::timestamp = without time zone) AND (sale_date <=3D '2009-07-06 00:00:00'::timestamp = without time zone) AND ((sale_channel)::text =3D 'RETAIL'::text))" " Filter: ((activity_type)::text =3D 'RETURN'::text)" "Total runtime: 6.583 ms" I think I've done what I could when it comes to altering statistics: For = example, activity_type and sale_channel have full statistics, and they are = rather independent as filtering mechanisms: If all Postgres did when = trying to estimate their total filtering capacity was just multiply the = frequency of each value, the estimates would not be far off. =20 The killer seems to be the row aggregation. There are about 95K different = values of sku_id in the sales table, and even the best seller items are a = very small percentage of all rows, so expecting the aggregation to = consolidate the rows 50:1 like it does in one of the explains above is a = pipe dream. I've increased statistics in sku_id into the three digits, but = results are not any better =20 schemaname;tablename;attname;null_frac;avg_width;n_distinct;most_common_fre= qs "reporting";"sales";"sku_id";0;11;58337;"{0.00364167,0.0027125,0.00230417,0= .00217083,0.00178333,0.001675,0.00136667,0.00135,0.0012875,0.0011875,...." =20 Is there any way I can coax Postgres into making a more realistic = aggregation estimate? I could just delay aggregation until the rest of the = data is joined, making the estimate's failure moot, but the price would be = quite hefty in some of the reports, which could return 20K products and = widths of over 150, so it's not optimal, especially when right now the = same query that can request 100 rows could end up requesting 80K. --=__Part2A002205.0__= Content-Type: text/html; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Description: HTML
I'm having some performance problems in a few sales reports running = on postgres 8.3, running on Redhat 4.1.2. The hardware is a bit old, but = it performs well enough. The reports are the typical sales = reporting fare: Gather the sales of a time period based some criteria, = aggregate them by product, and then join with a bunch of other tables to = display different kinds of product information.
 
I'll spare you all the pain of looking at the entire queries: The = ultimate issue appears to be the same: The innermost table of the queries = is an inline view, which aggregates the data by product. It runs rather = quickly, but postgres underestimates the number of rows that come out of = it, making the rest of the query plan rather suboptimal. The inline view = look like this
 
select sku_id, sum(rs.price) as dollarsSold, sum(rs.quantity) as = units 
  from reporting.sales rs
  where rs.sale_dat= e between ? AND ?  group by sku_id
 
In some cases, we see extra conditions aside of the dates, but they = have the same shape. Barring a massive date range, the rest = of the filters are less selective than the date, so postgres uses an index = on sale_date,sku_id. I have increased the statistics calculations on = sale_date quite a bit to make sure Postgres makes decent row estimates.The = problem is in the aggregation:
 
"HashAggregate  (cost=3D54545.20..54554.83 rows=3D642 width=3D24)= (actual time=3D87.945..98.219 rows=3D11462 loops=3D1)"
"  = ->  Index Scan using reporting_sales_sale_date_idx on sales = rs  (cost=3D0.00..54288.63 rows=3D34209 width=3D24) (actual time=3D0.0= 42..34.194 rows=3D23744 loops=3D1)"
"      = ;  Index Cond: ((sale_date >=3D '2009-07-01 00:00:00'::timestamp = without time zone) AND (sale_date <=3D '2009-07-06 00:00:00'::timestamp = without time zone))"
"Total runtime: 10.110 ms"
 
As you an seem the Index scan's estimate is pretty close when I use a = single condition, but the aggregate estimate is off by a factor of 20. = When I add further conditions, the estimate just gets worse and worse.
 
"HashAggregate  (cost=3D8894.83..8894.85 rows=3D1 width=3D24) = (actual time=3D6.444..6.501 rows=3D92 loops=3D1)"
"  ->  = Index Scan using reporting_sales_sale_date_sku_id_idx on sales rs  = (cost=3D0.00..8894.76 rows=3D9 width=3D24) (actual time=3D0.103..6.278 = rows=3D94 loops=3D1)"
"        Index = Cond: ((sale_date >=3D '2009-07-01 00:00:00'::timestamp without time = zone) AND (sale_date <=3D '2009-07-06 00:00:00'::timestamp without time = zone) AND ((sale_channel)::text =3D 'RETAIL'::text))"
"   = ;     Filter: ((activity_type)::text =3D 'RETURN'::text= )"
"Total runtime: 6.583 ms"
I think I've done what I could when it comes to altering statistics: = For example, activity_type and sale_channel have full statistics, and they = are rather independent as filtering mechanisms: If all Postgres did when = trying to estimate their total filtering capacity was just multiply the = frequency of each value, the estimates would not be far off.
 
The killer seems to be the row aggregation. There are about  95K = different values of sku_id in the sales table, and even the best seller = items are a very small percentage of all rows, so expecting the = aggregation to consolidate the rows 50:1 like it does in one of the = explains above is a pipe dream. I've increased statistics in = sku_id into the three digits, but results are not any better
 
schemaname;tablename;attname;null_frac;avg_width;n_distinct;most_commo= n_freqs
"reporting";"sales";"sku_id";0;11;58337;"{0.00364167,0.0027125,0.00230= 417,0.00217083,0.00178333,0.001675,0.00136667,0.00135,0.0012875,0.0011875,.= ..."
 
Is there any way I can coax Postgres into making a more realistic = aggregation estimate? I could just delay aggregation until the rest of the = data is joined, making the estimate's failure moot, but the price would be = quite hefty in some of the reports, which could return 20K products and = widths of over 150, so it's not optimal, especially when right now the = same query that can request 100 rows could end up requesting 80K.
--=__Part2A002205.0__=--