> Csaba, please can you copy that data into fresh tables, re-ANALYZE and
> then re-post the EXPLAINs, with stats data.
Here you go, fresh experiment attached.
Cheers,
Csaba.
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
