db=# \d temp_table_a Table "public.temp_table_a" Column | Type | Modifiers --------+--------+----------- a | bigint | not null b | bigint | not null Indexes: "temp_pk_table_a" PRIMARY KEY, btree (a, b) db=# \d temp_table_b1 Table "public.temp_table_b1" Column | Type | Modifiers --------+--------+----------- b | bigint | not null Indexes: "temp_pk_table_b1" PRIMARY KEY, btree (b) db=# \d temp_table_b2 Table "public.temp_table_b2" Column | Type | Modifiers --------+--------+----------- b | bigint | not null Indexes: "temp_pk_table_b2" PRIMARY KEY, btree (b) Foreign-key constraints: "temp_fk_table_b2_b1" FOREIGN KEY (b) REFERENCES temp_table_b1(b) db=# analyze verbose temp_table_a; INFO: analyzing "public.temp_table_a" INFO: "temp_table_a": scanned 3000 of 655299 pages, containing 1887000 live rows and 0 dead rows; 3000 rows in sample, 412183071 estimated total rows db=# analyze verbose temp_table_b1; INFO: analyzing "public.temp_table_b1" INFO: "temp_table_b1": scanned 3000 of 57285 pages, containing 2232000 live rows and 0 dead rows; 3000 rows in sample, 42620040 estimated total rows db=# analyze verbose temp_table_b2; INFO: analyzing "public.temp_table_b2" INFO: "temp_table_b2": scanned 57 of 57 pages, containing 41967 live rows and 0 dead rows; 3000 rows in sample, 41967 estimated total rows db=# select * from pg_stats where tablename = 'temp_table_a'; schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation ------------+--------------+---------+-----------+-----------+------------+-------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+------------- public | temp_table_a | a | 0 | 8 | 1261 | {700004117,700022128,700002317,700009411,700023682,700006025,700002843,700014833,700006505,700008694} | {0.015,0.0116667,0.00966667,0.00966667,0.00966667,0.009,0.00866667,0.008,0.00766667,0.00766667} | {70010872,700000035,700003086,700005843,700008974,700011369,700013305,700015988,700019048,700022257,800003151} | 0.850525 public | temp_table_a | b | 0 | 8 | -1 | | | {41708986,700707712,803042997,7004741432,7007455842,7009719495,7013869874,7016501748,7019139288,7025078292,7037930133} | 0.646759 db=# select * from pg_stats where tablename = 'temp_table_b1'; schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation ------------+---------------+---------+-----------+-----------+------------+------------------+-------------------+-------------------------------------------------------------------------------------------------------------------------+------------- public | temp_table_b1 | b | 0 | 8 | -1 | | | {41719236,801608645,7003211583,7007403678,7011591097,7016707278,7021089839,7025573684,7029316772,7033888226,8002470137} | 0.343186 db=# select * from pg_stats where tablename = 'temp_table_b2'; schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation ------------+---------------+---------+-----------+-----------+------------+------------------+-------------------+---------------------------------------------------------------------------------------------------------------------------+------------- public | temp_table_b2 | b | 0 | 8 | -1 | | | {700533334,7000352893,7000357745,7000362304,7000367025,7000371629,7000376587,7000381229,7009567724,7023749432,7034300740} | -0.216073 db=# prepare test_001(bigint) as db-# SELECT tb.* db-# FROM temp_table_a ta db-# JOIN temp_table_b2 tb ON ta.b=tb.b db-# WHERE ta.a = $1 db-# ORDER BY ta.a, ta.b db-# limit 10; db=# explain analyze execute test_001(31855344); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=152569.69..152569.72 rows=10 width=24) (actual time=581.776..581.776 rows=0 loops=1) -> Sort (cost=152569.69..152569.78 rows=33 width=24) (actual time=581.774..581.774 rows=0 loops=1) Sort Key: ta.a, ta.b -> Nested Loop (cost=0.00..152568.86 rows=33 width=24) (actual time=581.763..581.763 rows=0 loops=1) -> Seq Scan on temp_table_b2 tb (cost=0.00..476.67 rows=41967 width=8) (actual time=0.005..69.190 rows=41967 loops=1) -> Index Scan using temp_pk_table_a on temp_table_a ta (cost=0.00..3.61 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=41967) Index Cond: ((ta.a = $1) AND (ta.b = "outer".b)) Total runtime: 581.844 ms (8 rows) db=# prepare test_002(bigint) as db-# SELECT tb.* db-# FROM temp_table_a ta db-# JOIN temp_table_b1 tb ON ta.b=tb.b db-# WHERE ta.a = $1 db-# ORDER BY ta.a, ta.b db-# limit 10; db=# explain analyze execute test_002(31855344); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..471.24 rows=10 width=24) (actual time=0.036..0.036 rows=0 loops=1) -> Nested Loop (cost=0.00..1592742.78 rows=33799 width=24) (actual time=0.033..0.033 rows=0 loops=1) -> Index Scan using temp_pk_table_a on temp_table_a ta (cost=0.00..315266.89 rows=326870 width=16) (actual time=0.023..0.023 rows=0 loops=1) Index Cond: (a = $1) -> Index Scan using temp_pk_table_b1 on temp_table_b1 tb (cost=0.00..3.90 rows=1 width=8) (never executed) Index Cond: ("outer".b = tb.b) Total runtime: 2.483 ms