Date: Fri, 6 Sep 2013 09:44:20 +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: <128353624.931378457060911.JavaMail.root@shiva>
In-Reply-To: <1431656319.911378456847221.JavaMail.root@shiva>
Subject: Re: 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 again, my mistake. I have found why there are not this optimization (thu=
s 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 applie=
d 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 WH=
ERE 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=3D15485.12..15490.08 rows=3D248 width=3D32)"
"  Filter: ((min(public.t_weighting.stat_item_start) >=3D '2013-08-01 00:00=
:00'::timestamp without time zone) AND (min(public.t_weighting.stat_item_st=
art) <=3D '2013-09-01 00:00:00'::timestamp without time zone))"
"  ->  Nested Loop  (cost=3D222.05..15441.65 rows=3D5796 width=3D32)"
"        ->  HashAggregate  (cost=3D8.47..8.48 rows=3D1 width=3D32)"
"              ->  Subquery Scan "ANY_subquery"  (cost=3D8.45..8.47 rows=3D=
1 width=3D32)"
"                    ->  HashAggregate  (cost=3D8.45..8.46 rows=3D1 width=
=3D24)"
"                          ->  Index Scan using idx_t_weighting_stat_item_s=
tart on t_weighting  (cost=3D0.00..8.44 rows=3D1 width=3D24)"
"                                Index Cond: ((stat_item_start >=3D '2013-0=
8-01 00:00:00'::timestamp without time zone) AND (stat_item_start <=3D '201=
3-09-01 00:00:00'::timestamp without time zone))"
"        ->  Bitmap Heap Scan on t_weighting  (cost=3D213.58..15360.73 rows=
=3D5796 width=3D32)"
"              Recheck Cond: ((public.t_weighting.stocktaking_id)::text =3D=
 ("ANY_subquery".stocktaking_id)::text)"
"              ->  Bitmap Index Scan on idx_t_weighting_stocktaking_id_user=
_id  (cost=3D0.00..212.13 rows=3D5796 width=3D0)"
"                    Index Cond: ((public.t_weighting.stocktaking_id)::text=
 =3D ("ANY_subquery".stocktaking_id)::text)"


This will be probably a little bit harder to use in planner in general mann=
er.

Best regards,
--
Ing. =C4=BDubom=C3=ADr Varga
+421 (0)908 541 700
varga@plaintext.sk
www.plaintext.sk

----- "=C4=BDubom=C3=ADr 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):
>=20
> explain
> SELECT stocktaking_id
> FROM t_weighting
> GROUP BY stocktaking_id
> HAVING MIN(stat_item_start) BETWEEN '2013-08-01' AND '2013-09-01';
>=20
> 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
> without time zone) AND (min(stat_item_start) <=3D '2013-09-01
> 00:00:00'::timestamp without time zone))"
> "  ->  Seq Scan on t_weighting  (cost=3D0.00..49002.39 rows=3D1437339
> width=3D32)"
>=20
> 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:
>=20
>=20
> 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';
>=20
> 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
> without time zone) AND (min(stat_item_start) <=3D '2013-09-01
> 00:00:00'::timestamp without time zone))"
> "  ->  Index Scan using idx_t_weighting_stat_item_start on t_weighting
>  (cost=3D0.00..8.44 rows=3D1 width=3D32)"
> "        Index Cond: ((stat_item_start >=3D '2013-08-01
> 00:00:00'::timestamp without time zone) AND (stat_item_start <=3D
> '2013-09-01 00:00:00'::timestamp without time zone))"
>=20
> 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.
>=20
> 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