-
Notifications
You must be signed in to change notification settings - Fork 69
Open
@thamerlan
Description
Добрый день.
Наткнулись на следующий баг.
Имеется сегментированная таблица, где все сегменты - это внешние таблицы на двух других 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
Metadata
Metadata
Assignees
Labels
No labels