Rewriting a query

Question

I have a database with the following structure (star schema):

facttable:

  pros integer
  mena integer
  me integer
  vahet double precision
  slutttp timestamp without time zone

Structure of each dimension table is:

  "name" character varying(40),
  id integer NOT NULL,
  CONSTRAINT dim_me_pkey PRIMARY KEY (id)

My fact table is huge with several billion rows and the dimension tables contain several thousand rows.

I use a tool to write queries (tableausoftware.com), this means that I cannot write my queries intelligently. And I have to use that tool. So my solution was to rewrite the queries which are generated by that tool.

For ex.

Original query:

select dim_me.name, 
       case when  
          dim_me.name like 'k%' then ‘something’ 
          else ‘egg’ 
       end as egg, 
       sum(facttable.vahet) 
from dim_me, facttable
where dim_me.id = facttable.me group by name;

Needless to say, such a query is simply hopelessly suboptimal. Therefore in order to increase the performance, I rewrite the above query as:

select dim_me.name, 
       case 
           when dim_me.name like 'k%' then 'something' 
           else 'egg' 
       end as egg, 
       sum_vahet 
 from (
    select me, 
           sum(vahet) as sum_vahet 
    from facttable
    group by me
 ) as result_table 
 inner join dim_me on (dim_me.id = result_table.me);

My question if there is better way to rewrite this query in order to gain performance speed.

Explain of both the query is as follows:

Explain of Original query


HashAggregate  (cost=409746.04..409746.83 rows=53 width=15)"
  ->  Hash Join  (cost=2.19..407307.98 rows=487612 width=15)"
        Hash Cond: (facttable.me = dim_me.id)"
        ->  Seq Scan on facttable  (cost=0.00..400601.12 rows=487612 width=12)"
        ->  Hash  (cost=1.53..1.53 rows=53 width=11)"
              ->  Seq Scan on dim_me  (cost=0.00..1.53 rows=53 width=11)"



Explain of rewritten query


Hash Join  (cost=403040.86..403043.19 rows=48 width=15)"
  Hash Cond: (dim_me.id = result_table.me)"
  ->  Seq Scan on dim_me  (cost=0.00..1.53 rows=53 width=11)"
  ->  Hash  (cost=403040.26..403040.26 rows=48 width=12)"
        ->  Subquery Scan result_table  (cost=403039.18..403040.26 rows=48 width=12)"
              ->  HashAggregate  (cost=403039.18..403039.78 rows=48 width=12)"
                    ->  Seq Scan on facttable  (cost=0.00..400601.12 rows=487612 width=12)"

Answer 1

Indexes are not going to help you.

dim_me has only 53 rows, so an index will not be faster than a sequential table scan. And as you process the whole facttable anyway, a sequential table scan will be the fastest possible way here, too. There is just no room for optimization in your query.

Of course all the basic stuff for optimization applies and might help you.

If you should, in fact, only be interested in some of the rows, you might want to add a WHERE clause to the SELECT on facttable. Then an index might help.