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