Hello Tom,
Sorry for the misleading. Could you try these two queries? I made the query even slower in latest version of postgres. These are information about how we set up evaluation environment and query result.
Thanks,
Jinho Jung
Install Multiple version of DBs in one machine
======================================
# Install 10.5
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" > /etc/apt/sources.list.d/pgdg_xenial.list'
$ sudo apt update
$ sudo apt-get install postgresql-10
# Install 9.6
$ sudo apt-get install postgresql-9.6
# Install 9.5
$ sudo apt-get install postgresql-9.5
# Install 9.4
$ sudo apt-get install postgresql-9.4 postgresql-contrib-9.4 libpq-dev postgresql-server-dev-9.4
# check
$ pg_lsclusters
Original regression query
==========================
explain analyze
select
1
from
information_schema.role_usage_grants as ref_2,
lateral (
select
max((null)) over (partition by ref_3.amopfamily) as c8
from
pg_catalog.pg_amop as ref_3
) as subq_0
;
ORIGINAL querying time
on old version(9.4/9.5): 5.7ms
on latest version(10): 91.76ms
CORRELATED query to maximize error
===================================
explain analyze
select *
from information_schema.role_usage_grants f1
where grantor =
( select max(ref_2.grantor)
from
information_schema.role_usage_grants as ref_2,
lateral (
select
max((null)) over (partition by ref_3.amopfamily) as c8
from
pg_catalog.pg_amop as ref_3
) as subq_0
where ref_2.object_catalog = f1.object_catalog
)
;
CORRELATED querying time
on old version(9.4/9.5): 0.6s
on latest version(10): 113s
188 times slower