search

postgrespro / pg_pathman

Partitioning tool for PostgreSQL
Other
575 stars 68 forks source link

Wrong execution with foreign tables #203

Open thamerlan opened 4 years ago

thamerlan commented 4 years ago

Добрый день. Наткнулись на следующий баг. Имеется сегментированная таблица, где все сегменты - это внешние таблицы на двух других PG серверах. Если сделать выборку с прямым указанием ID, то выбираются все записи:

SELECT r.id
      ,r.finished_at
FROM   archive_schema.history_table r
WHERE r.id = 1000000019024;

      id       |       finished_at       
---------------+-------------------------
 1000000019024 | 2018-06-12 13:39:24.408
 1000000019024 | 2019-01-31 09:02:21.369
(2 rows)

Но если выбирать эти же данные путём JOIN с другой таблицей, например:

create table test_id_table (test_id BIGINT);
insert into test_id_table values (1000000019024);

То возвращается только одна запись:

   SELECT r.id
         ,r.finished_at
   FROM   archive_schema.history_table r
          INNER JOIN
          test_id_table d ON r.id = d.test_id;

      id       |       finished_at       
---------------+-------------------------
 1000000019024 | 2018-06-12 13:39:24.408
(1 row)

План выполнения в данном случае такой:

                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=100.44..21767878.00 rows=2260 width=16) (actual time=61.174..61.177 rows=1 loops=1)
   Buffers: shared hit=1
   ->  Seq Scan on test_id_table d  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.017..0.020 rows=1 loops=1)
         Buffers: shared hit=1
   ->  Append  (cost=100.44..9630.91 rows=88 width=16) (actual time=61.141..61.141 rows=1 loops=1)
         ->  Foreign Scan on history_table_30 r  (cost=100.44..109.28 rows=1 width=16) (actual time=1.622..1.622 rows=0 loops=1)
         ->  Foreign Scan on history_table_31 r_1  (cost=100.44..108.88 rows=1 width=16) (actual time=1.396..1.396 rows=0 loops=1)
         ->  Foreign Scan on history_table_32 r_2  (cost=100.44..109.28 rows=1 width=16) (actual time=1.319..1.319 rows=0 loops=1)
         ->  Foreign Scan on history_table_33 r_3  (cost=100.44..109.28 rows=1 width=16) (actual time=0.861..0.862 rows=0 loops=1)
         ->  Foreign Scan on history_table_34 r_4  (cost=100.45..109.28 rows=1 width=16) (actual time=1.270..1.270 rows=0 loops=1)
         ->  Foreign Scan on history_table_35 r_5  (cost=100.45..109.68 rows=1 width=16) (actual time=0.912..0.912 rows=0 loops=1)
         ->  Foreign Scan on history_table_36 r_6  (cost=100.45..109.29 rows=1 width=16) (actual time=1.337..1.338 rows=0 loops=1)
         ->  Foreign Scan on history_table_37 r_7  (cost=100.45..109.68 rows=1 width=16) (actual time=0.758..0.758 rows=0 loops=1)
         ->  Foreign Scan on history_table_38 r_8  (cost=100.45..108.88 rows=1 width=16) (actual time=1.196..1.196 rows=0 loops=1)
         ->  Foreign Scan on history_table_39 r_9  (cost=100.45..109.29 rows=1 width=16) (actual time=0.914..0.915 rows=0 loops=1)
         ->  Foreign Scan on history_table_40 r_10  (cost=100.45..109.29 rows=1 width=16) (actual time=0.988..0.988 rows=0 loops=1)
         ->  Foreign Scan on history_table_41 r_11  (cost=100.45..108.89 rows=1 width=16) (actual time=0.856..0.856 rows=0 loops=1)
         ->  Foreign Scan on history_table_42 r_12  (cost=100.45..109.68 rows=1 width=16) (actual time=1.111..1.112 rows=0 loops=1)
         ->  Foreign Scan on history_table_43 r_13  (cost=100.45..109.68 rows=1 width=16) (actual time=0.944..0.945 rows=0 loops=1)
         ->  Foreign Scan on history_table_44 r_14  (cost=100.57..109.81 rows=1 width=16) (actual time=1.059..1.060 rows=0 loops=1)
         ->  Foreign Scan on history_table_45 r_15  (cost=100.57..109.41 rows=1 width=16) (actual time=0.924..0.925 rows=0 loops=1)
         ->  Foreign Scan on history_table_46 r_16  (cost=100.57..109.81 rows=1 width=16) (actual time=1.617..1.617 rows=0 loops=1)
         ->  Foreign Scan on history_table_47 r_17  (cost=100.57..109.81 rows=1 width=16) (actual time=0.901..0.901 rows=0 loops=1)
         ->  Foreign Scan on history_table_48 r_18  (cost=100.57..109.81 rows=1 width=16) (actual time=1.612..1.612 rows=0 loops=1)
         ->  Foreign Scan on history_table_49 r_19  (cost=100.57..109.81 rows=1 width=16) (actual time=1.300..1.300 rows=0 loops=1)
         ->  Foreign Scan on history_table_50 r_20  (cost=100.57..109.81 rows=1 width=16) (actual time=1.049..1.050 rows=0 loops=1)
         ->  Foreign Scan on history_table_51 r_21  (cost=100.57..109.01 rows=1 width=16) (actual time=1.152..1.153 rows=0 loops=1)
         ->  Foreign Scan on history_table_52 r_22  (cost=100.58..109.42 rows=1 width=16) (actual time=0.945..0.945 rows=0 loops=1)
         ->  Foreign Scan on history_table_53 r_23  (cost=100.58..109.42 rows=1 width=16) (actual time=1.184..1.184 rows=0 loops=1)
         ->  Foreign Scan on history_table_54 r_24  (cost=100.58..109.42 rows=1 width=16) (actual time=0.975..0.975 rows=0 loops=1)
         ->  Foreign Scan on history_table_55 r_25  (cost=100.58..109.81 rows=1 width=16) (actual time=1.259..1.259 rows=0 loops=1)
         ->  Foreign Scan on history_table_56 r_26  (cost=100.58..109.42 rows=1 width=16) (actual time=0.930..0.931 rows=0 loops=1)
         ->  Foreign Scan on history_table_57 r_27  (cost=100.58..109.81 rows=1 width=16) (actual time=1.308..1.308 rows=0 loops=1)
         ->  Foreign Scan on history_table_58 r_28  (cost=100.58..109.81 rows=1 width=16) (actual time=1.209..1.209 rows=0 loops=1)
         ->  Foreign Scan on history_table_59 r_29  (cost=100.58..109.81 rows=1 width=16) (actual time=1.071..1.071 rows=0 loops=1)
         ->  Foreign Scan on history_table_60 r_30  (cost=100.58..109.81 rows=1 width=16) (actual time=1.360..1.360 rows=0 loops=1)
         ->  Foreign Scan on history_table_61 r_31  (cost=100.58..109.42 rows=1 width=16) (actual time=1.353..1.353 rows=0 loops=1)
         ->  Foreign Scan on history_table_62 r_32  (cost=100.58..109.42 rows=1 width=16) (actual time=1.146..1.146 rows=0 loops=1)
         ->  Foreign Scan on history_table_63 r_33  (cost=100.58..109.42 rows=1 width=16) (actual time=1.330..1.330 rows=0 loops=1)
         ->  Foreign Scan on history_table_64 r_34  (cost=100.58..109.42 rows=1 width=16) (actual time=1.192..1.192 rows=0 loops=1)
         ->  Foreign Scan on history_table_65 r_35  (cost=100.58..109.42 rows=1 width=16) (actual time=1.097..1.098 rows=0 loops=1)
         ->  Foreign Scan on history_table_66 r_36  (cost=100.58..109.42 rows=1 width=16) (actual time=1.174..1.174 rows=0 loops=1)
         ->  Foreign Scan on history_table_67 r_37  (cost=100.58..109.42 rows=1 width=16) (actual time=1.521..1.522 rows=0 loops=1)
         ->  Foreign Scan on history_table_68 r_38  (cost=100.58..109.42 rows=1 width=16) (actual time=1.194..1.194 rows=0 loops=1)
         ->  Foreign Scan on history_table_69 r_39  (cost=100.58..109.82 rows=1 width=16) (actual time=1.072..1.072 rows=0 loops=1)
         ->  Foreign Scan on history_table_70 r_40  (cost=100.58..109.82 rows=1 width=16) (actual time=1.161..1.161 rows=0 loops=1)
         ->  Foreign Scan on history_table_71 r_41  (cost=100.58..109.02 rows=1 width=16) (actual time=1.228..1.228 rows=0 loops=1)
         ->  Foreign Scan on history_table_72 r_42  (cost=100.58..109.42 rows=1 width=16) (actual time=1.171..1.172 rows=0 loops=1)
         ->  Foreign Scan on history_table_73 r_43  (cost=100.58..109.42 rows=1 width=16) (actual time=1.119..1.119 rows=0 loops=1)
         ->  Foreign Scan on history_table_74 r_44  (cost=100.58..109.82 rows=1 width=16) (actual time=0.986..0.986 rows=0 loops=1)
         ->  Foreign Scan on history_table_75 r_45  (cost=100.58..109.82 rows=1 width=16) (actual time=1.040..1.041 rows=0 loops=1)
         ->  Foreign Scan on history_table_76 r_46  (cost=100.58..109.42 rows=1 width=16) (actual time=1.480..1.480 rows=0 loops=1)
         ->  Foreign Scan on history_table_77 r_47  (cost=100.58..109.02 rows=1 width=16) (actual time=1.029..1.030 rows=0 loops=1)
         ->  Foreign Scan on history_table_78 r_48  (cost=100.58..109.42 rows=1 width=16) (actual time=1.119..1.119 rows=0 loops=1)
         ->  Foreign Scan on history_table_79 r_49  (cost=100.58..109.42 rows=1 width=16) (actual time=1.102..1.103 rows=0 loops=1)
         ->  Foreign Scan on history_table_80 r_50  (cost=100.58..109.82 rows=1 width=16) (actual time=1.074..1.074 rows=0 loops=1)
         ->  Foreign Scan on history_table_81 r_51  (cost=100.58..109.42 rows=1 width=16) (actual time=0.861..0.861 rows=0 loops=1)
         ->  Foreign Scan on history_table_82 r_52  (cost=100.58..109.82 rows=1 width=16) (actual time=1.266..1.266 rows=1 loops=1)
         ->  Foreign Scan on history_table_83 r_53  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_84 r_54  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_85 r_55  (cost=100.58..109.82 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_86 r_56  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_87 r_57  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_88 r_58  (cost=100.58..109.82 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_89 r_59  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_90 r_60  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_91 r_61  (cost=100.58..109.82 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_92 r_62  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_93 r_63  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_94 r_64  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_95 r_65  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_96 r_66  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_97 r_67  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_98 r_68  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_99 r_69  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_100 r_70  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_1100 r_71  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_1101 r_72  (cost=100.58..109.82 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_1134 r_73  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table_1135 r_74  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__181119_181126 r_75  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__181126_181203 r_76  (cost=100.58..109.82 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__181203_181210 r_77  (cost=100.58..109.82 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__181210_181217 r_78  (cost=100.58..109.82 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__181217_181224 r_79  (cost=100.58..109.82 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__181224_181231 r_80  (cost=100.58..109.02 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__181231_190107 r_81  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__190107_190114 r_82  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__190114_190121 r_83  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__190121_190128 r_84  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__190128_190204 r_85  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__190204_190211 r_86  (cost=100.58..109.42 rows=1 width=16) (never executed)
         ->  Foreign Scan on history_table__190211_190218 r_87  (cost=100.58..109.42 rows=1 width=16) (never executed)
 Planning time: 187.035 ms
 Execution time: 86.801 ms
(95 rows)

Если создать такую же сегментированную таблицу без pathman, то выбираются все 2 строки.

Environment

      extname       | extrelocatable | extversion |    extconfig    | extcondition 
--------------------+----------------+------------+-----------------+--------------
 plpgsql            | f              | 1.0        |                 | 
 pg_hashids         | t              | 1.2.1      |                 | 
 pg_repack          | f              | 1.4.3      |                 | 
 dblink             | t              | 1.2        |                 | 
 pg_pathman         | f              | 1.4        | {158084,158095} | {"",""}
 pg_stat_statements | t              | 1.5        |                 | 
 pgcrypto           | t              | 1.3        |                 | 
 pglogical          | f              | 2.2.0      |                 | 
 postgres_fdw       | t              | 1.0        |                 | 
 uuid-ossp          | t              | 1.1        |                 | 
 pg_cron            | f              | 1.1        |                 |

PostgreSQL 10.5 (Ubuntu 10.5-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

 get_pathman_lib_version 
-------------------------
 1.4.12
thamerlan commented 4 years ago

Есть ли какие-то идеи по этой проблеме?