On Tue, Jun 7, 2016 at 12:31 PM, Michael Paquier
<[email protected]> wrote:
> On Tue, Jun 7, 2016 at 12:28 AM, Alvaro Herrera
> <[email protected]> wrote:
>> Tom Lane wrote:
>>> Alvaro Herrera <[email protected]> writes:
>>
>>> > I can't imagine that the server is avoiding hash aggregation on a 1MB
>>> > work_mem limit for data that's a few dozen of bytes. Is it really doing
>>> > that?
>>>
>>> Yup:
>>
>> Aha. Thanks for testing.
>>
>>> Now that you mention it, this does seem a bit odd, although I remember
>>> that there's a pretty substantial fudge factor in there when we have
>>> no statistics (which we don't in this example). If I ANALYZE ctv_data
>>> then it sticks to the hashagg plan all the way down to 64kB work_mem.
>>
>> Hmm, so we could solve the complaint by adding an ANALYZE. I'm open to
>> that; other opinions?
>
> We could just enforce work_mem to 64kB and then reset it.
Or just set up work_mem to a wanted value for the duration of the run
of psql_crosstab. Attached is my proposal.
--
Michael
diff --git a/src/test/regress/expected/psql_crosstab.out b/src/test/regress/expected/psql_crosstab.out
index a9c20a1..57b68bb 100644
--- a/src/test/regress/expected/psql_crosstab.out
+++ b/src/test/regress/expected/psql_crosstab.out
@@ -10,6 +10,8 @@ VALUES
('v0','h4','dbl', -3, '2014-12-15'),
('v0',NULL,'qux', 5, '2014-07-15'),
('v1','h2','quux',7, '2015-04-04');
+-- ensure plan consistency across the test
+SET work_mem = '64kB';
-- running \crosstabview after query uses query in buffer
SELECT v, EXTRACT(year FROM d), count(*)
FROM ctv_data
@@ -127,8 +129,8 @@ GROUP BY v, h ORDER BY h,v
\crosstabview v h i
v | h0 | h1 | h2 | h4 | #null#
----+--------+----+----+----+--------
- v1 | #null# | | 3 +| |
- | | | 7 | |
+ v1 | #null# | | 7 +| |
+ | | | 3 | |
v2 | | 3 | | |
v0 | | | | 4 +| 5
| | | | -3 |
@@ -143,8 +145,8 @@ FROM ctv_data GROUP BY v, h ORDER BY h,v
----+------+-----+-----
h0 | baz | |
h1 | | bar |
- h2 | foo +| |
- | quux | |
+ h2 | quux+| |
+ | foo | |
h4 | | | qux+
| | | dbl
| | | qux
@@ -156,8 +158,8 @@ FROM ctv_data GROUP BY v, h ORDER BY h,v
\crosstabview 1 "h" 4
v | h0 | h1 | h2 | h4 |
----+-----+-----+------+-----+-----
- v1 | baz | | foo +| |
- | | | quux | |
+ v1 | baz | | quux+| |
+ | | | foo | |
v2 | | bar | | |
v0 | | | | qux+| qux
| | | | dbl |
diff --git a/src/test/regress/sql/psql_crosstab.sql b/src/test/regress/sql/psql_crosstab.sql
index 43c959b..cb43556 100644
--- a/src/test/regress/sql/psql_crosstab.sql
+++ b/src/test/regress/sql/psql_crosstab.sql
@@ -12,6 +12,9 @@ VALUES
('v0',NULL,'qux', 5, '2014-07-15'),
('v1','h2','quux',7, '2015-04-04');
+-- ensure plan consistency across the test
+SET work_mem = '64kB';
+
-- running \crosstabview after query uses query in buffer
SELECT v, EXTRACT(year FROM d), count(*)
FROM ctv_data
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
