Thread: [COMMITTERS] pgsql: Adapt python regression tests to 69f4b9c85f16.

Adapt python regression tests to 69f4b9c85f16.

Hopefully this'll unbreak the buildfarm.

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/8b07aee8c5d803801c00103f0d61e32356aaf29c

Modified Files
--------------
src/pl/plpython/expected/plpython_setof.out | 7 ++-----
1 file changed, 2 insertions(+), 5 deletions(-)

Andres Freund <andres@anarazel.de> writes:
> Adapt python regression tests to 69f4b9c85f16.

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

            regards, tom lane

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

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

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
GROUPBY 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
GROUPBY 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

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
GROUPBY 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