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_date 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=54545.20..54554.83 rows=642 width=24) (actual time=87.945..98.219 rows=11462 loops=1)"
" -> Index Scan using reporting_sales_sale_date_idx on sales rs (cost=0.00..54288.63 rows=34209 width=24) (actual time=0.042..34.194 rows=23744 loops=1)"
" Index Cond: ((sale_date >= '2009-07-01 00:00:00'::timestamp without time zone) AND (sale_date <= '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=8894.83..8894.85 rows=1 width=24) (actual time=6.444..6.501 rows=92 loops=1)"
" -> Index Scan using reporting_sales_sale_date_sku_id_idx on sales rs (cost=0.00..8894.76 rows=9 width=24) (actual time=0.103..6.278 rows=94 loops=1)"
" Index Cond: ((sale_date >= '2009-07-01 00:00:00'::timestamp without time zone) AND (sale_date <= '2009-07-06 00:00:00'::timestamp without time zone) AND ((sale_channel)::text = 'RETAIL'::text))"
" Filter: ((activity_type)::text = '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_common_freqs
"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,...."
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.