I'm looking for ideas how to speed-up my query which is based on two tables only. I've added indexes, run vacuum.


CREATE TABLE vintage_unit
  id integer NOT NULL,
  vintage_unit_date date,
  origination_month timestamp with time zone,
  product character varying,
  customer_type character varying,
  balance numeric,
  CONSTRAINT pk_id_loan PRIMARY KEY (id)

CREATE INDEX id_vintage_unit_date  ON vintage_unit USING btree (vintage_unit_date);   
CREATE INDEX ind_customer_type ON vintage_unit USING btree (customer_type COLLATE pg_catalog."default");
CREATE INDEX ind_origination_month ON vintage_unit USING btree  (origination_month);    
CREATE INDEX ind_product ON vintage_unit USING btree (product COLLATE pg_catalog."default");

CREATE TABLE performance_event
  id integer,
  event_date date

CREATE INDEX ind_event_date ON performance_event USING btree (event_date);
CREATE INDEX ind_id ON performance_event USING btree (id);

In both tables there is 10,000 rows.

Explain plan: http://explain.depesz.com/s/2di

Query: http://sqlfiddle.com/#!1/edece/1

PostgreSQL 9.1.9

Note: In the query there is function time_distance:

CREATE OR REPLACE FUNCTION time_distance(to_date date, from_date date, granularity character varying)
  RETURNS integer AS

  distance  int;


   if granularity = 'month' then
         distance := (extract(months from age(date_trunc('month',to_date), date_trunc('month',from_date))))::int + 12*(extract(years from age(date_trunc('month',to_date), date_trunc('month',from_date))))::int;
   elsif granularity = 'quarter' then
       distance := trunc(((extract(months from age(date_trunc('quarter',to_date), date_trunc('quarter',from_date)))) + 12*(extract(years from age(date_trunc('quarter',to_date), date_trunc('quarter',from_date)))))/3)::integer;
   elsif granularity = 'year' then
       distance := ((trunc(extract(years from age(date_trunc('month',to_date), date_trunc('month',from_date)))) ))::integer;
       distance:= -1;
   end if;

    return distance;

  COST 100;


  with vintage_descriptors as (
      origination_month,product,customer_type,balance ,
      generate_series(date_trunc('month',vintage_unit_date),max_vintage_date,'1 month')::date as event_date,
      time_distance(generate_series(date_trunc('month',vintage_unit_date),max_vintage_date,'1 month')::date, vintage_unit_date,'month') as distance
        origination_month,product,customer_type,balance ,
        vintage_unit_date, max_vintage_date
        (select max(event_date) as max_vintage_date from performance_event) x
      group by 
        origination_month,product,customer_type,balance ,
    ) a

  ,vintage_unit_sums as(
        origination_month,product,customer_type,balance ,
        sum(1::int) as vintage_unit_weight,
        count(*) as vintage_unit_count
      group by 
        origination_month,product,customer_type,balance ,

  ,performance_event_sums as(
        origination_month,product,customer_type,balance ,
        date_trunc('month',event_date)::date as event_date, 
        sum(1::int) as event_weight
        join performance_event ve using (id)
      group by 
        origination_month,product,customer_type,balance , 

  ,vintage_csums as (
          over(partition by origination_month,product,customer_type,balance ,vintage_unit_date order by event_date) as event_weight_csum
        vintage_descriptors vd
        left join performance_event_sums vs using (origination_month,product,customer_type,balance ,vintage_unit_date,event_date)

  ,aggregation as (
       origination_month,product,customer_type,balance ,
        sum(vintage_unit_weight) vintage_unit_weight,
        sum(vintage_unit_count) vintage_unit_count,
        sum(coalesce(event_weight,0)) as event_weight,
        sum(coalesce(event_weight,0)) / sum(vintage_unit_weight) as event_weight_pct,
        sum(coalesce(event_weight_csum,0)) as event_weight_csum,
        sum(coalesce(event_weight_csum,0))/sum(coalesce(vintage_unit_weight,0)) as event_weight_csum_pct
        vintage_descriptors  vd
        join vintage_unit_sums using  (origination_month,product,customer_type,balance , vintage_unit_date)
        left join vintage_csums using (origination_month,product,customer_type,balance , vintage_unit_date, event_date)
     group by
        origination_month,product,customer_type,balance ,
     order by
        origination_month,product,customer_type,balance ,

  select * , row_number() over(partition by origination_month,product,customer_type , distance order by balance) as rn from aggregation 

Query Explanation The purpose of this query is to calculate data for vintage analysis. Generally it works with the following data structure:

General data structure input

The query I'm using here is actually dynamically created on the fly based on user input.


  • every combination of origination_month, product, customer_type and balance is one group I would like to observe (statistics are calculated in CTE vintage_unit_sums)
  • every group has number of events (aggregated in performance_event_sums)
  • the goal is to calculate ratio of events which happened after certain number of months after vintage_unit_date (this is expressed as distance between vintage_unit_date and event_date and calculated using time_distance function) and number of units (rows) in given group, but only those rows where event could potentially happen in the past, where last available datapoint is considered to be maximum value of vintage_unit_date accross whole dataset. (Example: If vintage_unit_date is 2013-10-01 for given group and maximum vintage_unit_date is 2013-11-01 then maximum available distance is 1. However when in the same group we have row with vintage_unit_date 2013-09-01` then maximum distance is 2. Then when calculating the above mentioned ratio for distance 2, I do not want to include row where maximum distance is lower than 2.)
  • cte vintage_descriptors is used to generate placeholder for every possible combination of available groups and potential distance (because it is not true that for every distance and every group there is an event).

About year ago I thought this can be solved in OLAP and have asked this question on StackOverflow. Since then I've created my own solution in R where SQL is created dynamically based on user input and result is send to PostgreSQL.

  • Please add the query to the body of the question, not just a link to an external site. – Colin 't Hart Oct 30 '13 at 9:35
  • @Colin'tHart Added, will keep your advice in mind for future questions. – Tomas Greif Oct 30 '13 at 9:53
  • As the query is not exactly trivial, could you add some explanation about what you try to achieve? – dezso Oct 30 '13 at 11:05
  • @dezso I've tried hard to explain this, hope this helps. – Tomas Greif Oct 30 '13 at 12:45
  • 1
    It looks like raising the work_mem by some MBs (say 30) would speed things up significantly. You could also remove the ORDER BY clause from the aggregation CTE. – dezso Oct 30 '13 at 21:07

It looks like raising the work_mem by some MBs (say 30) would speed things up significantly. You could also remove the ORDER BY clause from the aggregation CTE. Similarly, the GROUP BY in the subquery of the first CTE looks useless. Also, sum(1) is basically a count on a non-null column, for example the column used in the inner join. Furthermore, it looks like you carry a bunch of nonaggregated and nongrouping columns through the CTEs - it is usually a good idea to join these in the latest possible step. This way you can decrease the memory consumption in certain steps. I mean, for example, the vd.* in vintage_csums.

  • Thank you. I've removed order by. I see no difference in performance between sum(1) and count(id). group by in the first CTE is there to get all unique combinations (distinct has the same performance). All columns in vd.* are actually used in join condition in the next step. – Tomas Greif Nov 6 '13 at 12:09
  • @TomasGreif and how about work_mem? – dezso Nov 6 '13 at 12:28
  • @deszo I can see some improvement using higher work_mem. See explain plan here for work_mem=30MB – Tomas Greif Nov 6 '13 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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