Hi again, my mistake. I have found why there are not this optimization (thus I have found other one, correct, see
bellow).I can have for example:
stocktaking_id | stat_item_start
------------------------------------
abc | 2013-01-01
abc | 2013-08-08
And when applied my "optimization", it will return me abc (minimum for abc is 2013-01-01 and it does not conform having
restriction,but I have applied where restriction to date which broke my result...)
Proper optimization should be:
explain
SELECT stocktaking_id
FROM t_weighting
--proper optimization restriction
WHERE stocktaking_id IN (SELECT DISTINCT stocktaking_id FROM t_weighting 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=15485.12..15490.08 rows=248 width=32)"
" Filter: ((min(public.t_weighting.stat_item_start) >= '2013-08-01 00:00:00'::timestamp without time zone) AND
(min(public.t_weighting.stat_item_start)<= '2013-09-01 00:00:00'::timestamp without time zone))"
" -> Nested Loop (cost=222.05..15441.65 rows=5796 width=32)"
" -> HashAggregate (cost=8.47..8.48 rows=1 width=32)"
" -> Subquery Scan "ANY_subquery" (cost=8.45..8.47 rows=1 width=32)"
" -> HashAggregate (cost=8.45..8.46 rows=1 width=24)"
" -> Index Scan using idx_t_weighting_stat_item_start on t_weighting (cost=0.00..8.44 rows=1
width=24)"
" 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))"
" -> Bitmap Heap Scan on t_weighting (cost=213.58..15360.73 rows=5796 width=32)"
" Recheck Cond: ((public.t_weighting.stocktaking_id)::text = ("ANY_subquery".stocktaking_id)::text)"
" -> Bitmap Index Scan on idx_t_weighting_stocktaking_id_user_id (cost=0.00..212.13 rows=5796 width=0)"
" Index Cond: ((public.t_weighting.stocktaking_id)::text = ("ANY_subquery".stocktaking_id)::text)"
This will be probably a little bit harder to use in planner in general manner.
Best regards,
--
Ing. Ľubomír Varga
+421 (0)908 541 700
varga@plaintext.sk
www.plaintext.sk
----- "Ľubomír Varga" <varga@plaintext.sk> wrote:
> 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@plaintext.sk
> www.plaintext.sk