Hi. I have one query which possibly is not optimized by planner (not using index for aggregate having clause
restriction):
explain
SELECT stocktaking_id
FROM t_weighting
GROUP BY stocktaking_id
HAVING MIN(stat_item_start) BETWEEN '2013-08-01' AND '2013-09-01';
with result:
"HashAggregate (cost=59782.43..59787.39 rows=248 width=32)"
" Filter: ((min(stat_item_start) >= '2013-08-01 00:00:00'::timestamp without time zone) AND (min(stat_item_start) <=
'2013-09-0100:00:00'::timestamp without time zone))"
" -> Seq Scan on t_weighting (cost=0.00..49002.39 rows=1437339 width=32)"
I have probably an obvious tough, that query will touch only rows with stat_item_start values only within given
constrainsin having clause. If (and only if) planner have some info that MIN and MAX aggregate functions could return
onlyone of values that comes into them, it can search only rows within given constraints in having part of select.
Somethinglike this:
explain
SELECT stocktaking_id
FROM t_weighting
--added restriction by hand:
WHERE stat_item_start BETWEEN '2013-08-01' AND '2013-09-01'
GROUP BY stocktaking_id
HAVING MIN(stat_item_start) BETWEEN '2013-08-01' AND '2013-09-01';
with result:
"HashAggregate (cost=8.45..8.47 rows=1 width=32)"
" Filter: ((min(stat_item_start) >= '2013-08-01 00:00:00'::timestamp without time zone) AND (min(stat_item_start) <=
'2013-09-0100:00:00'::timestamp without time zone))"
" -> Index Scan using idx_t_weighting_stat_item_start on t_weighting (cost=0.00..8.44 rows=1 width=32)"
" Index Cond: ((stat_item_start >= '2013-08-01 00:00:00'::timestamp without time zone) AND (stat_item_start <=
'2013-09-0100:00:00'::timestamp without time zone))"
Is this optimization by planner possible, or it is already have been done on newer DB version (I am using PostgreSQL
8.4.13)?IMHO it should be added into planner if possible for all built in aggregate functions.
Best regards,
--
Ing. Ľubomír Varga
+421 (0)908 541 700
varga@plaintext.sk
www.plaintext.sk
