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-01 00: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 constrains in having clause. If (and only if) planner have some info that MIN and MAX aggregate functions could return only one of values that comes into them, it can search only rows within given constraints in having part of select. Something like 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-01 00: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-01 00: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 va...@plaintext.sk www.plaintext.sk -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
