From: Ľubomír Varga <varga@plaintext.sk>
To: pgsql-performance@postgresql.org
Cc: info <info@plaintext.sk>
Subject: planner and having clausule
Date: Fri, 6 Sep 2013 09:05:22 +0100 (GMT+01:00)
Message-ID: <1186425372.701378454722489.JavaMail.root@shiva> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: varga@plaintext.sk, info@plaintext.sk
Subject: Re: planner and having clausule
In-Reply-To: <1186425372.701378454722489.JavaMail.root@shiva>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox