Performance difference with versions of Postgres/Postgis

[fractalf]

Hi community!

I'd like to register an issue with Postgis regarding performance in different versions of Postgres/Postgis.

I'm testing this out before we build a full solution and got some strange different response times running a query on different versions.

I test this in docker using 3 setups:

1.

FROM postgres:11.9
ENV POSTGIS_MAJOR 3
ENV POSTGIS_VERSION 3.0.2+dfsg-2.pgdg90+1

⇒ This gives me a response time of about 0.05s

2.

FROM postgres:12
ENV POSTGIS_MAJOR 3
ENV POSTGIS_VERSION 3.0.2+dfsg-2.pgdg100+1

⇒ This gives me a response time of about 0.5s

3.

FROM postgres:12
ENV POSTGIS_MAJOR 2.5
ENV POSTGIS_VERSION 2.5.5+dfsg-1.pgdg100+1

⇒ This gives me a response time of about 0.05s

I found this huge increase (almost 10x) in response time very strange.

This is more or less my test data/query:

CREATE TABLE "zones" (
  "id" serial NOT NULL,
  "name" character varying NOT NULL,
  "geo" geometry NOT NULL
);
CREATE TABLE "points" (
  "id" serial NOT NULL,
  "name" character varying NOT NULL,
  "geo" geometry NOT NULL
);
INSERT INTO zones (name, geo) VALUES
    ('Foo', 'POLYGON(
        (
            63.43277965483068 10.354270935058594,
            63.43009239981209 10.353584289550781,
            63.42533150079501 10.367660522460936,
            63.421337878804195 10.386199951171873,
            63.40474303024033 10.385856628417969,
            63.394904513399645 10.387916564941406,
            63.4155000456553 10.458297729492186,
            63.428403139082626 10.472373962402344,
            63.43945831122457 10.46945571899414,
            63.44759342069812 10.462932586669922,
            63.45035576238835 10.45074462890625,
            63.44797709520402 10.42276382446289,
            63.44053289210481 10.398731231689453,
            63.434008030326964 10.362682342529297,
            63.43277965483068 10.354270935058594
        )
    )');
# Insert 10000 random points from a generated sql file


select count(*)
from points as p
join zones as z
on ST_WITHIN(p.geo, z.geo)

[Algunenano]

This is missing the reproduction steps and the plans behind the queries, otherwise there is nothing we can do.