optimizer_first_rows.sql

--create two identical partitioned tables with 100 partitions each
create table a (id bigint, primary key (id)) partition by range (id);
create table a0 partition of a for values from ('0') to ('100000');
create table a1 partition of a for values from ('100000') to ('200000');
create table a2 partition of a for values from ('200000') to ('300000');
create table a3 partition of a for values from ('300000') to ('400000');
create table a4 partition of a for values from ('400000') to ('500000');
create table a5 partition of a for values from ('500000') to ('600000');
create table a6 partition of a for values from ('600000') to ('700000');
create table a7 partition of a for values from ('700000') to ('800000');
create table a8 partition of a for values from ('800000') to ('900000');
create table a9 partition of a for values from ('900000') to ('1000000');
create table a10 partition of a for values from ('1000000') to ('1100000');
create table a11 partition of a for values from ('1100000') to ('1200000');
create table a12 partition of a for values from ('1200000') to ('1300000');
create table a13 partition of a for values from ('1300000') to ('1400000');
create table a14 partition of a for values from ('1400000') to ('1500000');
create table a15 partition of a for values from ('1500000') to ('1600000');
create table a16 partition of a for values from ('1600000') to ('1700000');
create table a17 partition of a for values from ('1700000') to ('1800000');
create table a18 partition of a for values from ('1800000') to ('1900000');
create table a19 partition of a for values from ('1900000') to ('2000000');
create table a20 partition of a for values from ('2000000') to ('2100000');
create table a21 partition of a for values from ('2100000') to ('2200000');
create table a22 partition of a for values from ('2200000') to ('2300000');
create table a23 partition of a for values from ('2300000') to ('2400000');
create table a24 partition of a for values from ('2400000') to ('2500000');
create table a25 partition of a for values from ('2500000') to ('2600000');
create table a26 partition of a for values from ('2600000') to ('2700000');
create table a27 partition of a for values from ('2700000') to ('2800000');
create table a28 partition of a for values from ('2800000') to ('2900000');
create table a29 partition of a for values from ('2900000') to ('3000000');
create table a30 partition of a for values from ('3000000') to ('3100000');
create table a31 partition of a for values from ('3100000') to ('3200000');
create table a32 partition of a for values from ('3200000') to ('3300000');
create table a33 partition of a for values from ('3300000') to ('3400000');
create table a34 partition of a for values from ('3400000') to ('3500000');
create table a35 partition of a for values from ('3500000') to ('3600000');
create table a36 partition of a for values from ('3600000') to ('3700000');
create table a37 partition of a for values from ('3700000') to ('3800000');
create table a38 partition of a for values from ('3800000') to ('3900000');
create table a39 partition of a for values from ('3900000') to ('4000000');
create table a40 partition of a for values from ('4000000') to ('4100000');
create table a41 partition of a for values from ('4100000') to ('4200000');
create table a42 partition of a for values from ('4200000') to ('4300000');
create table a43 partition of a for values from ('4300000') to ('4400000');
create table a44 partition of a for values from ('4400000') to ('4500000');
create table a45 partition of a for values from ('4500000') to ('4600000');
create table a46 partition of a for values from ('4600000') to ('4700000');
create table a47 partition of a for values from ('4700000') to ('4800000');
create table a48 partition of a for values from ('4800000') to ('4900000');
create table a49 partition of a for values from ('4900000') to ('5000000');
create table a50 partition of a for values from ('5000000') to ('5100000');
create table a51 partition of a for values from ('5100000') to ('5200000');
create table a52 partition of a for values from ('5200000') to ('5300000');
create table a53 partition of a for values from ('5300000') to ('5400000');
create table a54 partition of a for values from ('5400000') to ('5500000');
create table a55 partition of a for values from ('5500000') to ('5600000');
create table a56 partition of a for values from ('5600000') to ('5700000');
create table a57 partition of a for values from ('5700000') to ('5800000');
create table a58 partition of a for values from ('5800000') to ('5900000');
create table a59 partition of a for values from ('5900000') to ('6000000');
create table a60 partition of a for values from ('6000000') to ('6100000');
create table a61 partition of a for values from ('6100000') to ('6200000');
create table a62 partition of a for values from ('6200000') to ('6300000');
create table a63 partition of a for values from ('6300000') to ('6400000');
create table a64 partition of a for values from ('6400000') to ('6500000');
create table a65 partition of a for values from ('6500000') to ('6600000');
create table a66 partition of a for values from ('6600000') to ('6700000');
create table a67 partition of a for values from ('6700000') to ('6800000');
create table a68 partition of a for values from ('6800000') to ('6900000');
create table a69 partition of a for values from ('6900000') to ('7000000');
create table a70 partition of a for values from ('7000000') to ('7100000');
create table a71 partition of a for values from ('7100000') to ('7200000');
create table a72 partition of a for values from ('7200000') to ('7300000');
create table a73 partition of a for values from ('7300000') to ('7400000');
create table a74 partition of a for values from ('7400000') to ('7500000');
create table a75 partition of a for values from ('7500000') to ('7600000');
create table a76 partition of a for values from ('7600000') to ('7700000');
create table a77 partition of a for values from ('7700000') to ('7800000');
create table a78 partition of a for values from ('7800000') to ('7900000');
create table a79 partition of a for values from ('7900000') to ('8000000');
create table a80 partition of a for values from ('8000000') to ('8100000');
create table a81 partition of a for values from ('8100000') to ('8200000');
create table a82 partition of a for values from ('8200000') to ('8300000');
create table a83 partition of a for values from ('8300000') to ('8400000');
create table a84 partition of a for values from ('8400000') to ('8500000');
create table a85 partition of a for values from ('8500000') to ('8600000');
create table a86 partition of a for values from ('8600000') to ('8700000');
create table a87 partition of a for values from ('8700000') to ('8800000');
create table a88 partition of a for values from ('8800000') to ('8900000');
create table a89 partition of a for values from ('8900000') to ('9000000');
create table a90 partition of a for values from ('9000000') to ('9100000');
create table a91 partition of a for values from ('9100000') to ('9200000');
create table a92 partition of a for values from ('9200000') to ('9300000');
create table a93 partition of a for values from ('9300000') to ('9400000');
create table a94 partition of a for values from ('9400000') to ('9500000');
create table a95 partition of a for values from ('9500000') to ('9600000');
create table a96 partition of a for values from ('9600000') to ('9700000');
create table a97 partition of a for values from ('9700000') to ('9800000');
create table a98 partition of a for values from ('9800000') to ('9900000');
create table a99 partition of a for values from ('9900000') to ('10000000');
create table a100 partition of a for values from ('10000000') to ('10100000');
create table b (id bigint, primary key (id)) partition by range (id);create table b0 partition of b for values from ('0') to ('100000');
create table b1 partition of b for values from ('100000') to ('200000');
create table b2 partition of b for values from ('200000') to ('300000');
create table b3 partition of b for values from ('300000') to ('400000');
create table b4 partition of b for values from ('400000') to ('500000');
create table b5 partition of b for values from ('500000') to ('600000');
create table b6 partition of b for values from ('600000') to ('700000');
create table b7 partition of b for values from ('700000') to ('800000');
create table b8 partition of b for values from ('800000') to ('900000');
create table b9 partition of b for values from ('900000') to ('1000000');
create table b10 partition of b for values from ('1000000') to ('1100000');
create table b11 partition of b for values from ('1100000') to ('1200000');
create table b12 partition of b for values from ('1200000') to ('1300000');
create table b13 partition of b for values from ('1300000') to ('1400000');
create table b14 partition of b for values from ('1400000') to ('1500000');
create table b15 partition of b for values from ('1500000') to ('1600000');
create table b16 partition of b for values from ('1600000') to ('1700000');
create table b17 partition of b for values from ('1700000') to ('1800000');
create table b18 partition of b for values from ('1800000') to ('1900000');
create table b19 partition of b for values from ('1900000') to ('2000000');
create table b20 partition of b for values from ('2000000') to ('2100000');
create table b21 partition of b for values from ('2100000') to ('2200000');
create table b22 partition of b for values from ('2200000') to ('2300000');
create table b23 partition of b for values from ('2300000') to ('2400000');
create table b24 partition of b for values from ('2400000') to ('2500000');
create table b25 partition of b for values from ('2500000') to ('2600000');
create table b26 partition of b for values from ('2600000') to ('2700000');
create table b27 partition of b for values from ('2700000') to ('2800000');
create table b28 partition of b for values from ('2800000') to ('2900000');
create table b29 partition of b for values from ('2900000') to ('3000000');
create table b30 partition of b for values from ('3000000') to ('3100000');
create table b31 partition of b for values from ('3100000') to ('3200000');
create table b32 partition of b for values from ('3200000') to ('3300000');
create table b33 partition of b for values from ('3300000') to ('3400000');
create table b34 partition of b for values from ('3400000') to ('3500000');
create table b35 partition of b for values from ('3500000') to ('3600000');
create table b36 partition of b for values from ('3600000') to ('3700000');
create table b37 partition of b for values from ('3700000') to ('3800000');
create table b38 partition of b for values from ('3800000') to ('3900000');
create table b39 partition of b for values from ('3900000') to ('4000000');
create table b40 partition of b for values from ('4000000') to ('4100000');
create table b41 partition of b for values from ('4100000') to ('4200000');
create table b42 partition of b for values from ('4200000') to ('4300000');
create table b43 partition of b for values from ('4300000') to ('4400000');
create table b44 partition of b for values from ('4400000') to ('4500000');
create table b45 partition of b for values from ('4500000') to ('4600000');
create table b46 partition of b for values from ('4600000') to ('4700000');
create table b47 partition of b for values from ('4700000') to ('4800000');
create table b48 partition of b for values from ('4800000') to ('4900000');
create table b49 partition of b for values from ('4900000') to ('5000000');
create table b50 partition of b for values from ('5000000') to ('5100000');
create table b51 partition of b for values from ('5100000') to ('5200000');
create table b52 partition of b for values from ('5200000') to ('5300000');
create table b53 partition of b for values from ('5300000') to ('5400000');
create table b54 partition of b for values from ('5400000') to ('5500000');
create table b55 partition of b for values from ('5500000') to ('5600000');
create table b56 partition of b for values from ('5600000') to ('5700000');
create table b57 partition of b for values from ('5700000') to ('5800000');
create table b58 partition of b for values from ('5800000') to ('5900000');
create table b59 partition of b for values from ('5900000') to ('6000000');
create table b60 partition of b for values from ('6000000') to ('6100000');
create table b61 partition of b for values from ('6100000') to ('6200000');
create table b62 partition of b for values from ('6200000') to ('6300000');
create table b63 partition of b for values from ('6300000') to ('6400000');
create table b64 partition of b for values from ('6400000') to ('6500000');
create table b65 partition of b for values from ('6500000') to ('6600000');
create table b66 partition of b for values from ('6600000') to ('6700000');
create table b67 partition of b for values from ('6700000') to ('6800000');
create table b68 partition of b for values from ('6800000') to ('6900000');
create table b69 partition of b for values from ('6900000') to ('7000000');
create table b70 partition of b for values from ('7000000') to ('7100000');
create table b71 partition of b for values from ('7100000') to ('7200000');
create table b72 partition of b for values from ('7200000') to ('7300000');
create table b73 partition of b for values from ('7300000') to ('7400000');
create table b74 partition of b for values from ('7400000') to ('7500000');
create table b75 partition of b for values from ('7500000') to ('7600000');
create table b76 partition of b for values from ('7600000') to ('7700000');
create table b77 partition of b for values from ('7700000') to ('7800000');
create table b78 partition of b for values from ('7800000') to ('7900000');
create table b79 partition of b for values from ('7900000') to ('8000000');
create table b80 partition of b for values from ('8000000') to ('8100000');
create table b81 partition of b for values from ('8100000') to ('8200000');
create table b82 partition of b for values from ('8200000') to ('8300000');
create table b83 partition of b for values from ('8300000') to ('8400000');
create table b84 partition of b for values from ('8400000') to ('8500000');
create table b85 partition of b for values from ('8500000') to ('8600000');
create table b86 partition of b for values from ('8600000') to ('8700000');
create table b87 partition of b for values from ('8700000') to ('8800000');
create table b88 partition of b for values from ('8800000') to ('8900000');
create table b89 partition of b for values from ('8900000') to ('9000000');
create table b90 partition of b for values from ('9000000') to ('9100000');
create table b91 partition of b for values from ('9100000') to ('9200000');
create table b92 partition of b for values from ('9200000') to ('9300000');
create table b93 partition of b for values from ('9300000') to ('9400000');
create table b94 partition of b for values from ('9400000') to ('9500000');
create table b95 partition of b for values from ('9500000') to ('9600000');
create table b96 partition of b for values from ('9600000') to ('9700000');
create table b97 partition of b for values from ('9700000') to ('9800000');
create table b98 partition of b for values from ('9800000') to ('9900000');
create table b99 partition of b for values from ('9900000') to ('10000000');
create table b100 partition of b for values from ('10000000') to ('10100000');

-- insert data
insert into a (id) (select generate_series(0, 10000000));
analyze a;
insert into b (id) (select generate_series(0, 10000000));
analyze b;

-- reproduce issue
set enable_partitionwise_join = on;
explain (analyze) select * from a left join b using (id) order by id desc limit 10000;
set enable_hashjoin = off;
set enable_mergejoin = off;
explain (analyze) select * from a left join b using (id) order by id desc limit 10000;