Explain plan issue with group by clause in postgresql 8.4

Question

Below is given detail explanation which is related to explain plan with group by clause problem.

table: web_categoryutfv1_24hr_ts_201209
columns: "5mintime",category,hits,bytes,appid
rows: 871
Indexes: "web_categoryutfv1_24hr_ts_201209_idx" btree ("5mintime")

I am running the following query:

select count(*) over () as t_totalcnt,
       max(hits) over () as t_maxhits,
       max(bytes) over () as t_maxbytes,
       *
from (
  select category,
         sum(hits) as hits,
         sum(bytes) as bytes
  from (
    select "5mintime",
           category,
           hits,
           bytes,
           appid,
           0 as tmpfield
    from web_categoryutfv1_24hr_ts_201209
    where "5mintime" >= '2012-09-12 00:00:00'
    and   "5mintime" < '2012-09-19 00:00:00'
  ) as tmp
  where "5mintime" >= '2012-09-12 00:00:00'
  and   "5mintime" <= '2012-09-18 23:59:59'
  and   appid in ('')
  group by category
  order by hits desc
) as foo limit 10

I got total row return 55 from t_totalcnt variable. Now I analyzed web_categoryutfv1_24hr_ts_201209 table and again run same query with explain

I get the following execution plan:

-> Limit  (cost=31.31..31.61 rows=10 width=580)
->  WindowAgg  (cost=31.31..32.03 rows=24 width=580)
->  Subquery Scan foo  (cost=31.31..31.61 ***rows=24*** width=580)
      ->  Sort  (cost=31.31..31.37 rows=24 width=31)
            Sort Key: (sum(web_categoryutfv1_24hr_ts_201209.hits))
            ->  HashAggregate  (cost=30.39..30.75 rows=24 width=31)
                  ->  Seq Scan on web_categoryutfv1_24hr_ts_201209  (cost=0.00..27.60 rows=373 width=31)
                      Filter: (("5mintime" >= '2012-09-12 00:00:00'::timestamp without time zone) AND ("5mintime" < '2012-09-19 00:00:00'::timestamp without time zone) AND ("5mintime" >= '2012-09-12 00:00:00'::timestamp without time zone) AND ("5mintime" <= '2012-09-18 23:59:59'::timestamp without time zone) AND ((appid)::text = ''::text))

Now I got explain plan out put HashAggregate (cost=30.39..30.75 rows=24 width=31) which says rows=24 while actually the total row return should be 55. When I remove group by clause from query i got 373 rows in explain plan output as well as acutal query execution.

So I want to know is there is some issue with explain plan and group by clause in query?

score 1 · Answer 1 · answered Sep 21 '12 at 07:05

The rows shown in an execution plan are estimates. As long as they are somewhere within the correct magnitude things are fine. If they are totally off that usually means your statistics are out of date.

It makes sense that removing the group by changes the expected number of rows as group by will reduce them.

So I don't see any problems there.

You can use explain analyze in order to compare actuals and real numbers in the execution plan.

Explain plan issue with group by clause in postgresql 8.4

1 Answers1