Return to Revisions

After some processing this boiled down to:

While your predicate d."SettlementPointName" = 'John' is filtering a single value for "SettlementPointName" anyway, simplify to:

SELECT count( d."SettlementPointPrice" < 10.5 OR NULL) AS da_00_10 , count(d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" < 20.5 OR NULL) AS da_11_20 , count(d."SettlementPointPrice" >= 20.5 AND d."SettlementPointPrice" < 30.5 OR NULL) AS da_21_30 FROM public.da d JOIN public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName") WHERE d."SettlementPointName" = 'John' AND d."DeliveryDate" >= '2015-02-01' AND d."DeliveryDate" <= '2015-02-20' AND r."DeliveryHour" = 14 AND date_part('hour', d."DeliveryHour") = r."DeliveryHour"; 

About the counting technique:

• For absolute performance, is SUM faster or COUNT?

Or better, yet, use the new aggregate filter technique in pg 9.4:

SELECT d."SettlementPointName" , count(*) FILTER (WHERE d."SettlementPointPrice" < 10.5) AS da_00_10 , count(*) FILTER (WHERE d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" < 20.5) AS da_11_20 , count(*) FILTER (WHERE d."SettlementPointPrice" >= 20.5 AND d."SettlementPointPrice" < 30.5) AS da_21_30 FROM public.da d JOIN public.rt_aggregate r USING ("DeliveryDate", "SettlementPointName") WHERE d."DeliveryDate" >= '2015-02-01' AND d."DeliveryDate" <= '2015-02-20' AND r."DeliveryHour" = 14 AND date_part('hour', d."DeliveryHour") = r."DeliveryHour" GROUP BY 1; 

This time, selecting all names and returning one row per name like you asked in the comment.

Details for FILTER:

• Return counts for multiple ranges in a single SELECT statement