Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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


  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 (, 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:

       case when  
 like 'k%' then ‘something’ 
          else ‘egg’ 
       end as egg, 
from dim_me, facttable
where = 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:

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

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: ( ="
        ->  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: ( ="
  ->  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)"
share|improve this question
when = 'k%' does something completely different than like 'k%' in the original query. Can you please post the execution plan (using explain analyze) for both statements? Ideally uploaded to – a_horse_with_no_name Jan 13 '12 at 10:37
Yes, that is true, the performance of = 'k%' and like 'k%' is completely different. And the performance now between the original query and rewritten query is almost same (using like for both query), but I would like to know, can we rewrite the original query in a way, that performance will be high? – rohita Jan 13 '12 at 11:24
unless you post the structure of the tables involved, the indexes defined and the execution plan there is not much we can do. Do you have an index on or – a_horse_with_no_name Jan 13 '12 at 11:32
I have edited my post, and added explain in it. However I do not have any indexes in both facttable and dimension table. The string value name is primary key in dimension table. – rohita Jan 13 '12 at 12:07
Try an index one and one on (although I doubt the one on dim_me helps as only 53 rows are involved). If you look at the output of explain analyze you can also see whether the planner's estimations are correct. – a_horse_with_no_name Jan 13 '12 at 12:37

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.