Skip to content

/ pg_pathman

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Wrong execution with foreign tables #203

Open
thamerlan opened this issue Aug 23, 2019 · 1 comment
Open

Wrong execution with foreign tables #203

thamerlan opened this issue Aug 23, 2019 · 1 comment

Comments

@thamerlan
Copy link

@thamerlan thamerlan commented Aug 23, 2019

Добрый день.
Наткнулись на следующий баг.
Имеется сегментированная таблица, где все сегменты - это внешние таблицы на двух других 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
Copy link
Author

@thamerlan thamerlan commented Dec 4, 2019

Есть ли какие-то идеи по этой проблеме?
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant
@thamerlan
You can’t perform that action at this time.