planner and having clausule

From: Ľubomír Varga <varga(at)plaintext(dot)sk>
To: pgsql-performance(at)postgresql(dot)org
Cc: info <info(at)plaintext(dot)sk>
Subject: planner and having clausule
Date: 2013-09-06 08:05:22
Message-ID: 1186425372.701378454722489.JavaMail.root@shiva
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-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(at)plaintext(dot)sk
www.plaintext.sk

Browse pgsql-performance by date

  From Date Subject
Next Message Ľubomír Varga 2013-09-06 08:44:20 Re: planner and having clausule
Previous Message Dimitri Fontaine 2013-09-06 07:23:15 Re: [PERFORM] encouraging index-only scans