Date: Fri, 6 Sep 2013 09:05:22 +0100 (GMT+01:00)
From: =?utf-8?Q?=C4=BDubom=C3=ADr_Varga?= <varga@plaintext.sk>
To: pgsql-performance@postgresql.org
Cc: info <info@plaintext.sk>
Message-ID: <1186425372.701378454722489.JavaMail.root@shiva>
Subject: planner and having clausule
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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=3D59782.43..59787.39 rows=3D248 width=3D32)"
"  Filter: ((min(stat_item_start) >=3D '2013-08-01 00:00:00'::timestamp wit=
hout time zone) AND (min(stat_item_start) <=3D '2013-09-01 00:00:00'::times=
tamp without time zone))"
"  ->  Seq Scan on t_weighting  (cost=3D0.00..49002.39 rows=3D1437339 width=
=3D32)"

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 o=
nly if) planner have some info that MIN and MAX aggregate functions could r=
eturn only one of values that comes into them, it can search only rows with=
in 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=3D8.45..8.47 rows=3D1 width=3D32)"
"  Filter: ((min(stat_item_start) >=3D '2013-08-01 00:00:00'::timestamp wit=
hout time zone) AND (min(stat_item_start) <=3D '2013-09-01 00:00:00'::times=
tamp without time zone))"
"  ->  Index Scan using idx_t_weighting_stat_item_start on t_weighting  (co=
st=3D0.00..8.44 rows=3D1 width=3D32)"
"        Index Cond: ((stat_item_start >=3D '2013-08-01 00:00:00'::timestam=
p without time zone) AND (stat_item_start <=3D '2013-09-01 00:00:00'::times=
tamp without time zone))"

Is this optimization by planner possible, or it is already have been done o=
n 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. =C4=BDubom=C3=ADr Varga
+421 (0)908 541 700
varga@plaintext.sk
www.plaintext.sk


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