Create the planner mechanism for optimizing simple MIN and MAX queries

into indexscans on matching indexes.  For the moment, it only handles
int4 and text datatypes; next step is to add a column to pg_aggregate
so that all MIN/MAX aggregates can be handled.  Per my recent proposal.

4 files changed, 78 insertions, 0 deletions

diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index d07c6d195b7..8aed1864034 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -293,3 +293,58 @@ FROM bool_test;
  t | t | f |   | f | t
 (1 row)
 
+--
+-- Test several cases that should be optimized into indexscans instead of
+-- the generic aggregate implementation.  We can't actually verify that they
+-- are done as indexscans, but we can check that the results are correct.
+--
+-- Basic cases
+select max(unique1) from tenk1;
+ max  
+------
+ 9999
+(1 row)
+
+select max(unique1) from tenk1 where unique1 < 42;
+ max 
+-----
+  41
+(1 row)
+
+select max(unique1) from tenk1 where unique1 > 42;
+ max  
+------
+ 9999
+(1 row)
+
+select max(unique1) from tenk1 where unique1 > 42000;
+ max 
+-----
+    
+(1 row)
+
+-- multi-column index (uses tenk1_thous_tenthous)
+select max(tenthous) from tenk1 where thousand = 33;
+ max  
+------
+ 9033
+(1 row)
+
+select min(tenthous) from tenk1 where thousand = 33;
+ min 
+-----
+  33
+(1 row)
+
+-- check parameter propagation into an indexscan subquery
+select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
+from int4_tbl;
+     f1      | gt 
+-------------+----
+           0 |  1
+      123456 |   
+     -123456 |  0
+  2147483647 |   
+ -2147483647 |  0
+(5 rows)
+
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 6643070e717..a7332c86716 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -12,6 +12,7 @@ CREATE INDEX onek_stringu1 ON onek USING btree(stringu1 name_ops);
 CREATE INDEX tenk1_unique1 ON tenk1 USING btree(unique1 int4_ops);
 CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops);
 CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops);
+CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous);
 CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops);
 CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops);
 CREATE INDEX tenk2_hundred ON tenk2 USING btree(hundred int4_ops);
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index d9fdcb502fb..b6aba0d66b7 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -180,3 +180,23 @@ SELECT
   BOOL_OR(NOT b2)  AS "f",
   BOOL_OR(NOT b3)  AS "t"
 FROM bool_test;
+
+--
+-- Test several cases that should be optimized into indexscans instead of
+-- the generic aggregate implementation.  We can't actually verify that they
+-- are done as indexscans, but we can check that the results are correct.
+--
+
+-- Basic cases
+select max(unique1) from tenk1;
+select max(unique1) from tenk1 where unique1 < 42;
+select max(unique1) from tenk1 where unique1 > 42;
+select max(unique1) from tenk1 where unique1 > 42000;
+
+-- multi-column index (uses tenk1_thous_tenthous)
+select max(tenthous) from tenk1 where thousand = 33;
+select min(tenthous) from tenk1 where thousand = 33;
+
+-- check parameter propagation into an indexscan subquery
+select f1, (select min(unique1) from tenk1 where unique1 > f1) AS gt
+from int4_tbl;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 6fa0b91e83c..71c0b0c2b0a 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -20,6 +20,8 @@ CREATE INDEX tenk1_unique2 ON tenk1 USING btree(unique2 int4_ops);
 
 CREATE INDEX tenk1_hundred ON tenk1 USING btree(hundred int4_ops);
 
+CREATE INDEX tenk1_thous_tenthous ON tenk1 (thousand, tenthous);
+
 CREATE INDEX tenk2_unique1 ON tenk2 USING btree(unique1 int4_ops);
 
 CREATE INDEX tenk2_unique2 ON tenk2 USING btree(unique2 int4_ops);