Take the 2-minute tour ×
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.

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)"
share|improve this question
3  
when dim_me.name = 'k%' does something completely different than dim_me.name like 'k%' in the original query. Can you please post the execution plan (using explain analyze) for both statements? Ideally uploaded to explain.depesz.com –  a_horse_with_no_name Jan 13 '12 at 10:37
    
Yes, that is true, the performance of dim_me.name = 'k%' and dim_me.name 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
1  
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 facctable.me or dim_me.name? –  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 facttable.me and one on dim_me.id (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
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.