{"id":24346,"date":"2023-04-14T13:20:44","date_gmt":"2023-04-14T11:20:44","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=24346"},"modified":"2023-04-14T13:20:46","modified_gmt":"2023-04-14T11:20:46","slug":"how-a-merge-join-produced-the-wrong-result-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/","title":{"rendered":"How a merge join produced the wrong result in PostgreSQL"},"content":{"rendered":"\n

This was a really interesting customer case and I learned a lot by troubleshooting this. The original request was: We have two databases connected via postgres_fdw<\/a> and when a merge join is chosen by PostgreSQL we get the wrong result. Setting enable_mergejoin<\/a> to off causes PostgreSQL to go for a hash join and the result is correct. Well, personally, I’ve never run into a situation where PostgreSQL did not give the correct result, but of course there are bugs. The questions was: Is it a bug, or is this expected behavior?<\/p>\n\n\n\n

As I did not have access to the customer’s environment the first step was to rebuild the setup and then to try to reproduce that locally. I went for two Debian 11<\/a> VMs and PostgreSQL 13.7<\/a> (this is the version which is used at the customer). This already was my first mistake, but more on that later.<\/p>\n\n\n\n

The SQL which caused all this is quite simple:<\/p>\n\n\n

\nSELECT * \n  FROM "rsu_adm"."data_2d_clb_global_product" f2\n  LEFT OUTER JOIN "rsu_adm"."clb_global_product" f1 on f1.cprd=f2.cprd\n WHERE f1.cprd is null;  \n<\/pre><\/div>\n\n\n
A simple join, nothing really special. The first table is this one:<\/p>\n\n\n
\nrsup1=# \\d "rsu_adm"."data_2d_clb_global_product"\n                     Table "rsu_adm.data_2d_clb_global_product"\n         Column          |           Type           | Collation | Nullable | Default \n-------------------------+--------------------------+-----------+----------+---------\n cprd                    | character varying        |           | not null | \n xtc_id                  | numeric                  |           |          | \n rprd                    | numeric                  |           |          | \n prdgalsts_id            | integer                  |           |          | \n dlz_last_transaction_ts | timestamp with time zone |           |          | \nIndexes:\n    "data_2d_clb_global_product_pkey" PRIMARY KEY, btree (cprd)\n    "data_2d_clb_global_product_idx4" btree (dlz_last_transaction_ts)\n<\/pre><\/div>\n\n\n
The second reference is a view:<\/p>\n\n\n
\nrsup1=# \\d "rsu_adm"."clb_global_product"\n                           View "rsu_adm.clb_global_product"\n         Column          |            Type             | Collation | Nullable | Default \n-------------------------+-----------------------------+-----------+----------+---------\n cprd                    | character varying           |           |          | \n xtc_id                  | numeric(10,0)               |           |          | \n rprd                    | numeric(10,0)               |           |          | \n prdgalsts_id            | numeric(10,0)               |           |          | \n dlz_last_transaction_ts | timestamp(5) with time zone |           |          | \n<\/pre><\/div>\n\n\n
The view references a foreign table:<\/p>\n\n\n
\nrsup1=# select definition \n          from pg_views \n         where viewname = 'clb_global_product';\n                   definition                   \n------------------------------------------------\n  SELECT clb_global_product.cprd,              +\n     clb_global_product.xtc_id,                +\n     clb_global_product.rprd,                  +\n     clb_global_product.prdgalsts_id,          +\n     clb_global_product.dlz_last_transaction_ts+\n    FROM ro_dlz.clb_global_product;\n(1 row)\n\nrsup1=# \\d ro_dlz.clb_global_product\n                                            Foreign table "ro_dlz.clb_global_product"\n         Column          |            Type             | Collation | Nullable | Default |               FDW options               \n-------------------------+-----------------------------+-----------+----------+---------+-----------------------------------------\n cprd                    | character varying           |           |          |         | (column_name 'cprd')\n xtc_id                  | numeric(10,0)               |           |          |         | (column_name 'xtc_id')\n rprd                    | numeric(10,0)               |           |          |         | (column_name 'rprd')\n prdgalsts_id            | numeric(10,0)               |           |          |         | (column_name 'prdgalsts_id')\n dlz_last_transaction_ts | timestamp(5) with time zone |           |          |         | (column_name 'dlz_last_transaction_ts')\nServer: tgt_srv\nFDW options: (schema_name 'ro_rsu', table_name 'clb_global_product')\n\nrsup1=# \n<\/pre><\/div>\n\n\n
Here is the definition of the foreign server, for completeness:<\/p>\n\n\n
\nrsup1=# \\des+\n                                                                                       List of foreign servers\n  Name   |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |                                             FDW options                                              | Description \n---------+----------+----------------------+-------------------+------+---------+------------------------------------------------------------------------------------------------------+-------------\n tgt_srv | postgres | postgres_fdw         |                   |      |         | (host '192.168.100.245', dbname 'dlzp1', port '5432', use_remote_estimate 'true', fetch_size '5000') | \n(1 row)\n<\/pre><\/div>\n\n\n
Having a look at the other instance, the foreign table references a view, here are the definitions of the view and the underlying table:<\/p>\n\n\n
\npostgres=# \\c dlzp1\nYou are now connected to database "dlzp1" as user "postgres".\ndlzp1=# \\d ro_rsu.clb_global_product\n                            View "ro_rsu.clb_global_product"\n         Column          |            Type             | Collation | Nullable | Default \n-------------------------+-----------------------------+-----------+----------+---------\n cprd                    | character varying           |           |          | \n xtc_id                  | numeric(10,0)               |           |          | \n rprd                    | numeric(10,0)               |           |          | \n prdgalsts_id            | numeric(10,0)               |           |          | \n dlz_last_transaction_ts | timestamp(5) with time zone |           |          | \n\ndlzp1=# select definition \n          from pg_views \n         where viewname = 'clb_global_product';\n               definition               \n----------------------------------------\n  SELECT clb_flbgpr.cprd,              +\n     clb_flbgpr.xtc_id,                +\n     clb_flbgpr.rprd,                  +\n     clb_flbgpr.prdgalsts_id,          +\n     clb_flbgpr.dlz_last_transaction_ts+\n    FROM dlz_clb.clb_flbgpr;\n(1 row)\n\ndlzp1=# \\d dlz_clb.clb_flbgpr\n                               Table "dlz_clb.clb_flbgpr"\n         Column          |            Type             | Collation | Nullable | Default \n-------------------------+-----------------------------+-----------+----------+---------\n cprd                    | character varying           |           | not null | \n fp_lwr_celsius          | numeric(3,0)                |           |          | \n fp_lwr_farenheit        | numeric(3,0)                |           |          | \n fp_last_upd             | date                        |           |          | \n win_indicator           | numeric(6,0)                |           |          | \n gpr_theme               | character varying(6)        |           |          | \n comparison_symbol       | character(1)                |           |          | \n cprd_as_previous        | character varying           |           |          | \n emp_user_id_as_pcfm     | character varying           |           |          | \n xtc_id                  | numeric(10,0)               |           |          | \n gpr_oral_care           | character(1)                |           |          | \n bunit_id                | numeric(10,0)               |           |          | \n remark_id               | numeric(10,0)               |           |          | \n win_them_indicator      | numeric(6,0)                |           |          | \n plr_id                  | numeric(10,0)               |           |          | \n stability_status        | character(1)                |           |          | \n cmr_frm_tested          | character(1)                |           |          | \n rprd                    | numeric(10,0)               |           |          | \n like_level_id           | numeric(10,0)               |           |          | \n lhpr_id                 | numeric(10,0)               |           |          | \n like_perf_lvl_id        | numeric(10,0)               |           |          | \n lhdncpr_id              | numeric(10,0)               |           |          | \n like_hdnc_lvl_id        | numeric(10,0)               |           |          | \n prdgalsts_id            | numeric(10,0)               |           |          | \n oil_stock_sts           | character(1)                |           |          | \n archive_status          | character(2)                |           |          | \n dlz_last_transaction_ts | timestamp(5) with time zone |           |          | \nIndexes:\n    "clb_flbgpr_pk" PRIMARY KEY, btree (cprd)\n    "clb_flbgpr_lst_trn" btree (dlz_last_transaction_ts)\nTriggers:\n    clb_flbgpr_ins BEFORE INSERT ON dlz_clb.clb_flbgpr FOR EACH ROW EXECUTE FUNCTION dlz_adm.fct_trg_setup_transaction_ts()\n    clb_flbgpr_upd BEFORE UPDATE ON dlz_clb.clb_flbgpr FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE FUNCTION dlz_adm.fct_trg_setup_transaction_ts()\n<\/pre><\/div>\n\n\n
What happened was this: When a merge join was chosen, this was the explain plan:<\/p>\n\n\n
\nrsup1=# explain (analyze, verbose) SELECT * FROM "rsu_adm"."data_2d_clb_global_product" f2\nLEFT OUTER JOIN "rsu_adm"."clb_global_product" f1 on f1.cprd=f2.cprd\nWHERE f1.cprd is null;    \n                                                                                                                  QUERY PLAN                                                                                                    \n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n Merge Anti Join  (cost=100.84..67203.45 rows=50713 width=122) (actual time=41.769..2452.910 rows=4708 loops=1)\n   Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts, clb_global_product.cprd, clb_global_product.xtc_id, clb_global_product.rprd, clb_global_product.prdgalsts_id, clb_global_product.dlz_last_t\n   Merge Cond: ((f2.cprd)::text = (clb_global_product.cprd)::text)\n   -&gt;  Index Scan using data_2d_clb_global_product_pkey on rsu_adm.data_2d_clb_global_product f2  (cost=0.42..2898.56 rows=101426 width=34) (actual time=0.036..29.311 rows=101426 loops=1)\n         Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts\n   -&gt;  Foreign Scan on ro_dlz.clb_global_product  (cost=100.42..52506.16 rows=923613 width=34) (actual time=41.683..2258.170 rows=923613 loops=1)\n         Output: clb_global_product.cprd, clb_global_product.xtc_id, clb_global_product.rprd, clb_global_product.prdgalsts_id, clb_global_product.dlz_last_transaction_ts\n         Remote SQL: SELECT cprd, xtc_id, rprd, prdgalsts_id, dlz_last_transaction_ts FROM ro_rsu.clb_global_product ORDER BY cprd ASC NULLS LAST\n Planning Time: 7.721 ms\n Execution Time: 2453.489 ms\n(10 rows)\n<\/pre><\/div>\n\n\n
There is nothing wrong with that in general, but this plan gave a result when it shouldn’t:<\/p>\n\n\n
\nrsup1=# SELECT * \n          FROM "rsu_adm"."data_2d_clb_global_product" f2\n          LEFT OUTER JOIN "rsu_adm"."clb_global_product" f1 \n            ON f1.cprd=f2.cprd\n         WHERE f1.cprd is null;    \n     cprd      | xtc_id |  rprd   | prdgalsts_id |   dlz_last_transaction_ts    | cprd | xtc_id | rprd | prdgalsts_id | dlz_last_transaction_ts \n---------------+--------+---------+--------------+------------------------------+------+--------+------+--------------+-------------------------\n 0027033       |        |  179722 |            1 | 2023-04-03 06:15:09.45135+02 |      |        |      |              | \n 0112113       |        | 3199208 |            1 | 2023-04-03 06:15:09.45135+02 |      |        |      |              | \n 0116713       |        | 2071012 |            1 | 2023-04-03 06:15:09.45135+02 |      |        |      |              | \n...\n<\/pre><\/div>\n\n\n
Disabling merge joins, which causes PostgreSQL to go for a hash join, gave the correct result:<\/p>\n\n\n
\nrsup1=# set enable_mergejoin = off;\nSET\nrsup1=# explain (analyze, verbose) SELECT * FROM "rsu_adm"."data_2d_clb_global_product" f2\nLEFT OUTER JOIN "rsu_adm"."clb_global_product" f1 on f1.cprd=f2.cprd\nWHERE f1.cprd is null;    \n                                                                                                                  QUERY PLAN                                                                                                    \n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n Hash Anti Join  (cost=60274.55..681118.72 rows=50713 width=122) (actual time=2432.850..2432.852 rows=0 loops=1)\n   Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts, clb_global_product.cprd, clb_global_product.xtc_id, clb_global_product.rprd, clb_global_product.prdgalsts_id, clb_global_product.dlz_last_t\n   Hash Cond: ((f2.cprd)::text = (clb_global_product.cprd)::text)\n   ->  Seq Scan on rsu_adm.data_2d_clb_global_product f2  (cost=0.00..1768.26 rows=101426 width=34) (actual time=0.034..5.166 rows=101426 loops=1)\n         Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts\n   ->  Hash  (cost=41513.39..41513.39 rows=923613 width=34) (actual time=2297.904..2297.905 rows=923613 loops=1)\n         Output: clb_global_product.cprd, clb_global_product.xtc_id, clb_global_product.rprd, clb_global_product.prdgalsts_id, clb_global_product.dlz_last_transaction_ts\n         Buckets: 131072  Batches: 16  Memory Usage: 4666kB\n         ->  Foreign Scan on ro_dlz.clb_global_product  (cost=100.00..41513.39 rows=923613 width=34) (actual time=33.391..2035.852 rows=923613 loops=1)\n               Output: clb_global_product.cprd, clb_global_product.xtc_id, clb_global_product.rprd, clb_global_product.prdgalsts_id, clb_global_product.dlz_last_transaction_ts\n               Remote SQL: SELECT cprd, xtc_id, rprd, prdgalsts_id, dlz_last_transaction_ts FROM ro_rsu.clb_global_product\n Planning Time: 6.572 ms\n Execution Time: 2433.502 ms\n(13 rows)\n\nrsup1=# SELECT * \n          FROM "rsu_adm"."data_2d_clb_global_product" f2\n          LEFT OUTER JOIN "rsu_adm"."clb_global_product" f1 \n            ON f1.cprd=f2.cprd\n         WHERE f1.cprd is null;    \n cprd | xtc_id | rprd | prdgalsts_id | dlz_last_transaction_ts | cprd | xtc_id | rprd | prdgalsts_id | dlz_last_transaction_ts \n------+--------+------+--------------+-------------------------+------+--------+------+--------------+-------------------------\n(0 rows)\n\nrsup1=# \n<\/pre><\/div>\n\n\n
No matter what I’ve tried, I was not able to reproduce this. On my local setup the merge join gave exactly the same result as the hash join. <\/p>\n\n\n\n
After several hours of testing this and that, it became somehow clear that it was related to sorting. A merge join sorts both sides of the joins before returning the results. The sort, in this case, was on a text based column: Merge Cond: ((f2.cprd)::text = (clb_global_product.cprd)::text).<\/p>\n\n\n\n
When we talk about sorting text based data in PostgreSQL this is always about collations<\/a>. But when I’ve looked at the collations used in both the source and the target instance, both were configured in the same way and there is no other collation defined on the column itself:<\/p>\n\n\n
\n-- source\nrsup1=# \\l\n                                  List of databases\n   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   \n-----------+----------+----------+-------------+-------------+-----------------------\n postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n rsup1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n(4 rows)\n\n-- target\npostgres=# \\l\n                                  List of databases\n   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   \n-----------+----------+----------+-------------+-------------+-----------------------\n dlzp1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n(4 rows)\n<\/pre><\/div>\n\n\n
What else could cause this weird behavior then? Finally I’ve asked the right question: What operating systems are these two PostgreSQL instances running on? It turned out that the source instance is running on RHEL<\/a> 7.9 and the target instance is running on RHEL<\/a> 8.7. Re-doing the same setup with those versions of RHEL I was able to re-produce it.<\/p>\n\n\n\n
One that was clear it was almost obvious that it some relates to the versions of glibc<\/a>. <\/p>\n\n\n\n
RHEL 7 uses version 2.17:<\/p>\n\n\n
\npostgres@rsu1p1:\/home\/postgres\/ [src] cat \/etc\/os-release \nNAME="Red Hat Enterprise Linux Server"\nVERSION="7.9 (Maipo)"\nID="rhel"\nID_LIKE="fedora"\nVARIANT="Server"\nVARIANT_ID="server"\nVERSION_ID="7.9"\nPRETTY_NAME="Red Hat Enterprise Linux"\nANSI_COLOR="0;31"\nCPE_NAME="cpe:\/o:redhat:enterprise_linux:7.9:GA:server"\nHOME_URL="https:\/\/www.redhat.com\/"\nBUG_REPORT_URL="https:\/\/bugzilla.redhat.com\/"\n\nREDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 7"\nREDHAT_BUGZILLA_PRODUCT_VERSION=7.9\nREDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"\nREDHAT_SUPPORT_PRODUCT_VERSION="7.9"\npostgres@rsu1p1:\/home\/postgres\/ [src] rpm -qa | grep glibc\nglibc-headers-2.17-326.el7_9.x86_64\nglibc-common-2.17-326.el7_9.x86_64\nglibc-2.17-326.el7_9.x86_64\nglibc-devel-2.17-326.el7_9.x86_64\n<\/pre><\/div>\n\n\n
RHEL 8 comes with version 2.28:<\/p>\n\n\n
\npostgres@dlzp1:\/home\/postgres\/ [tgt] cat \/etc\/os-release \nNAME="Red Hat Enterprise Linux"\nVERSION="8.7 (Ootpa)"\nID="rhel"\nID_LIKE="fedora"\nVERSION_ID="8.7"\nPLATFORM_ID="platform:el8"\nPRETTY_NAME="Red Hat Enterprise Linux 8.7 (Ootpa)"\nANSI_COLOR="0;31"\nCPE_NAME="cpe:\/o:redhat:enterprise_linux:8::baseos"\nHOME_URL="https:\/\/www.redhat.com\/"\nDOCUMENTATION_URL="https:\/\/access.redhat.com\/documentation\/red_hat_enterprise_linux\/8\/"\nBUG_REPORT_URL="https:\/\/bugzilla.redhat.com\/"\n\nREDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 8"\nREDHAT_BUGZILLA_PRODUCT_VERSION=8.7\nREDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"\nREDHAT_SUPPORT_PRODUCT_VERSION="8.7"\n13:08:10 postgres@dlzp1:\/home\/postgres\/ [tgt] rpm -qa | grep glibc\nglibc-langpack-en-2.28-211.el8.x86_64\nglibc-devel-2.28-211.el8.x86_64\nglibc-2.28-211.el8.x86_64\nglibc-common-2.28-211.el8.x86_64\nglibc-gconv-extra-2.28-211.el8.x86_64\n<\/pre><\/div>\n\n\n
This can easily be proven by sorting these simple strings on both versions:<\/p>\n\n\n
\n-- source, RHEL 7.8\nCREATE TABLE\npostgres=# INSERT INTO t1 VALUES ('1-a'), ('1a'), ('1-aa');\nINSERT 0 3\npostgres=# SELECT * FROM t1 ORDER BY c1;\n  c1  \n------\n 1a\n 1-a\n 1-aa\n(3 rows)\n\n-- target, RHEL 8.7\npostgres=# CREATE TABLE t1 (c1 varchar PRIMARY KEY);\nCREATE TABLE\npostgres=# INSERT INTO t1 VALUES ('1-a'), ('1a'), ('1-aa');\nINSERT 0 3\npostgres=# SELECT * FROM t1 ORDER BY c1;\n  c1  \n------\n 1-a\n 1a\n 1-aa\n(3 rows)\n<\/pre><\/div>\n\n\n
Lessons learned: If you connect two instances with the foreign data wrapper for PostgreSQL, make sure that both operating systems come with the same version of glibc, otherwise you might not get what you expect.<\/p>\n","protected":false},"excerpt":{"rendered":"
This was a really interesting customer case and I learned a lot by troubleshooting this. The original request was: We have two databases connected via postgres_fdw and when a merge join is chosen by PostgreSQL we get the wrong result. Setting enable_mergejoin to off causes PostgreSQL to go for a hash join and the result […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[2602],"type_dbi":[],"class_list":["post-24346","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql-2"],"acf":[],"yoast_head":"\nHow a merge join produced the wrong result in PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How a merge join produced the wrong result in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"This was a really interesting customer case and I learned a lot by troubleshooting this. The original request was: We have two databases connected via postgres_fdw and when a merge join is chosen by PostgreSQL we get the wrong result. Setting enable_mergejoin to off causes PostgreSQL to go for a hash join and the result […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-14T11:20:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-04-14T11:20:46+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"How a merge join produced the wrong result in PostgreSQL\",\"datePublished\":\"2023-04-14T11:20:44+00:00\",\"dateModified\":\"2023-04-14T11:20:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/\"},\"wordCount\":538,\"commentCount\":2,\"keywords\":[\"postgresql\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/\",\"name\":\"How a merge join produced the wrong result in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2023-04-14T11:20:44+00:00\",\"dateModified\":\"2023-04-14T11:20:46+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How a merge join produced the wrong result in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How a merge join produced the wrong result in PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"How a merge join produced the wrong result in PostgreSQL","og_description":"This was a really interesting customer case and I learned a lot by troubleshooting this. The original request was: We have two databases connected via postgres_fdw and when a merge join is chosen by PostgreSQL we get the wrong result. Setting enable_mergejoin to off causes PostgreSQL to go for a hash join and the result […]","og_url":"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2023-04-14T11:20:44+00:00","article_modified_time":"2023-04-14T11:20:46+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"How a merge join produced the wrong result in PostgreSQL","datePublished":"2023-04-14T11:20:44+00:00","dateModified":"2023-04-14T11:20:46+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/"},"wordCount":538,"commentCount":2,"keywords":["postgresql"],"articleSection":["Database Administration & Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/","name":"How a merge join produced the wrong result in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-04-14T11:20:44+00:00","dateModified":"2023-04-14T11:20:46+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/how-a-merge-join-produced-the-wrong-result-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How a merge join produced the wrong result in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/24346","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=24346"}],"version-history":[{"count":16,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/24346\/revisions"}],"predecessor-version":[{"id":24579,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/24346\/revisions\/24579"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=24346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=24346"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=24346"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=24346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}