pgsql: Adapt python regression tests to 69f4b9c85f16.

Andres Freund <andres@anarazel.de> writes:

Adapt python regression tests to 69f4b9c85f16.

Drat. I tested everything *but* plpython. You too, evidently :-(

regards, tom lane

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

On 2017-01-18 19:34:52 -0500, Tom Lane wrote:

Andres Freund <andres@anarazel.de> writes:

Adapt python regression tests to 69f4b9c85f16.

Drat. I tested everything *but* plpython. You too, evidently :-(

Yea. I had taken it out of my configure invoking script because of a
packaging bug a while back :(.

Termite is also failing due to differing row orders from the rest of the
animals. I'd intentionally left those undefined, because I wanted some
queries without an ORDER BY. Haven't decided what the best fix is yet.

Andres

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Andres Freund <andres@anarazel.de> writes:

Termite is also failing due to differing row orders from the rest of the
animals. I'd intentionally left those undefined, because I wanted some
queries without an ORDER BY. Haven't decided what the best fix is yet.

Looks to me like all the bigendian critters are unhappy with that.
The previous plan (trying it on 9.6) was

GroupAggregate (cost=20.68..22.77 rows=400 width=52)
Group Key: dataa, (unnest('{1,1,3}'::integer[]))
-> Sort (cost=20.68..20.69 rows=4 width=40)
Sort Key: dataa, (unnest('{1,1,3}'::integer[]))
-> Seq Scan on few (cost=0.00..20.64 rows=4 width=40)
Filter: (id = 1)

and now we get

HashAggregate (cost=27.66..27.71 rows=4 width=52)
Group Key: dataa, unnest('{1,1,3}'::integer[])
-> ProjectSet (cost=0.00..22.66 rows=400 width=40)
-> Seq Scan on few (cost=0.00..20.62 rows=4 width=36)
Filter: (id = 1)

This is a good thing: it's coming up with a saner rowcount estimate,
ie it realizes that the sort or hash is going to see a number of rows
inflated by the SRF, and that's prompting it to go with a hashagg not
a sortagg. But then you get platform-dependent output order from
the hashagg.

If you don't want an ORDER BY, maybe turn off enable_hashagg for
these queries? But you'll get the same plan either way.

regards, tom lane

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

I wrote:

If you don't want an ORDER BY, maybe turn off enable_hashagg for
these queries? But you'll get the same plan either way.

Or not ... I forgot it has a better model of the rowcount changes now:

regression=# explain SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa, unnest('{1,1,3}'::int[]) order by few.dataa, unnest('{1,1,3}'::int[]);
QUERY PLAN
-----------------------------------------------------------------------
Sort (cost=27.75..27.76 rows=4 width=52)
Sort Key: dataa, (unnest('{1,1,3}'::integer[]))
-> HashAggregate (cost=27.66..27.71 rows=4 width=52)
Group Key: dataa, unnest('{1,1,3}'::integer[])
-> ProjectSet (cost=0.00..22.66 rows=400 width=40)
-> Seq Scan on few (cost=0.00..20.62 rows=4 width=36)
Filter: (id = 1)
(7 rows)
regression=# set enable_hashagg TO 0;
SET
regression=# explain SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa, unnest('{1,1,3}'::int[]);
QUERY PLAN
-----------------------------------------------------------------------
GroupAggregate (cost=39.94..45.99 rows=4 width=52)
Group Key: dataa, (unnest('{1,1,3}'::integer[]))
-> Sort (cost=39.94..40.94 rows=400 width=40)
Sort Key: dataa, (unnest('{1,1,3}'::integer[]))
-> ProjectSet (cost=0.00..22.66 rows=400 width=40)
-> Seq Scan on few (cost=0.00..20.62 rows=4 width=36)
Filter: (id = 1)
(7 rows)

So which plan would you rather test?

regards, tom lane

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Hi,

On 2017-01-18 21:09:52 -0500, Tom Lane wrote:

I wrote:

If you don't want an ORDER BY, maybe turn off enable_hashagg for
these queries? But you'll get the same plan either way.

Or not ... I forgot it has a better model of the rowcount changes now:

regression=# set enable_hashagg TO 0;
SET
regression=# explain SELECT few.dataa, count(*), min(id), max(id), unnest('{1,1,3}'::int[]) FROM few WHERE few.id = 1 GROUP BY few.dataa, unnest('{1,1,3}'::int[]);
QUERY PLAN
-----------------------------------------------------------------------
GroupAggregate (cost=39.94..45.99 rows=4 width=52)
Group Key: dataa, (unnest('{1,1,3}'::integer[]))
-> Sort (cost=39.94..40.94 rows=400 width=40)
Sort Key: dataa, (unnest('{1,1,3}'::integer[]))
-> ProjectSet (cost=0.00..22.66 rows=400 width=40)
-> Seq Scan on few (cost=0.00..20.62 rows=4 width=36)
Filter: (id = 1)
(7 rows)

So which plan would you rather test?

That looks good to me. Will add.

Thanks,

Andres

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers