planner and having clausule

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
varga@xxxxxxxxxxxx
www.plaintext.sk


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux