PostgreSQL:为什么从同一个源表中“select into”比“select count”更快?

将PG从11升级到15后,我遇到了以下情况:

旧(工作)设置: Postgres 11 PostGIS 2.5

设置: Postgres 15 PostGIS 3.3

表_1(约14.000.000行)

当我这样做的时候 select count(*) from table_1 它似乎永远不会结束。 当我这样做的时候 select * into table_2 from table_1 然后 select count(*) from table_2 ,它将在大约15分钟内完成。

具体声明如下:

-- this never stops
select count(*)
FROM table_1 t1
INNER JOIN table_1 t2 ON (ST_Equals(t1.wkb_geometry, t2.wkb_geometry))
WHERE t1.gid > t2.gid
;

-- this works
select *
into table_2
from table_1 
;
select count(*)
FROM table_2 t1
INNER JOIN table_2 t2 ON (ST_Equals(t1.wkb_geometry, t2.wkb_geometry))
WHERE t1.gid > t2.gid
;

在这两种情况下,都会在wkb_geometry上创建一个索引,如下所示:

CREATE INDEX table_1_geom ON table_1 
USING gist (wkb_geometry)
TABLESPACE pgdata;

我已经在桌子上真空地重新创建了索引,但什么都没用。

更新: 我发现问题出在gid列的pkey上。在选择进入。。。table2在gid上没有pkey。如果我删除table_1上的pkey,它也会工作。那么,在PG版本15中添加pkey差异会发生什么呢?有什么想法吗?

更新2: 表_1的DDL/SQL

SELECT ST_MakeLine(sp,ep) AS wkb_geometry, 
         ogc_fid,
         ...
    INTO table_1
    FROM
       (SELECT
          ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,
          ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep,
          ogc_fid,
          ...
        FROM
           (SELECT (ST_Dump(ST_Boundary(wkb_geometry))).geom, 
                   ogc_fid,
                   ...
           FROM another_table
           ) AS linestrings
        ) AS segments;
    
    ALTER TABLE table_1 ADD COLUMN gid serial NOT NULL;
    --ALTER TABLE table_1 ADD CONSTRAINT table_1_pk PRIMARY KEY (gid);
    CREATE INDEX table_1_geom ON table_1 USING gist (wkb_geometry) TABLESPACE pgdata;