Not able to understand co-relation between cost keyword in explain plan with time

Question

When I run below query

explain
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 get the below output

  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))

When I have run above query without the explain tag. I get output with in 1 seconds, while here cost=31.31..31.61.

Anybody please help me to understood what is cost keyword means in explain plan I mean units of cost keyword in explain plan?

Answer 1

Cost is the query planner's estimate of how difficult an operation is or how long it will take to perform. It's based on some machine-level parameters -- how long a disk seek will take versus a streaming read, for example -- along with table-level information like how big each row is, how many rows there are, or the distribution of values in each column. There are no units, and the resulting cost values are arbitrary. Costs are the metric PostgreSQL uses to figure out how to execute a query; it will consider the myriad ways in which to execute your query and choose the plan with the lowest cost. For more specifics on cost calculations, see Planner Cost Constraints.

Assuming you're using the default settings, a cost this low for a sequential scan suggests to me that PostgreSQL thinks there aren't many rows in that table. The fact that it's taking a full second to run suggests that there are, in fact, a lot of rows in that table. You can tell PostgreSQL to collect new statistics on that table by saying ANALYZE web_categoryutfv1_24hr_ts_201209. The pg_autovacuum process should regularly collect statistics anyway, but maybe you're on an older version of PostgreSQL, or it hasn't run in a while, or who knows; regardless, there's no harm in doing again by hand.

If PostgreSQL thinks that table is small, it'll prefer a sequential scan over using indexes, because a sequential read of the whole table is faster than an index scan followed by a bunch of random reads. On the other hand, if PostgreSQL thinks the table is large, it will likely be faster to reference an index on 5mintime and/or appid, assuming that said index will allow it to exclude many rows. If you have no such index, consider creating one.

One last thing: EXPLAIN has a big brother named EXPLAIN ANALYZE. While EXPLAIN shows you the query plan PostgreSQL will choose along with the costs that guided its decision, EXPLAIN ANALYZE actually executes the query and shows you how long each component took to run. See EXPLAIN for more.