On 25.07.2017 01:25, David G. Johnston wrote:
CREATE TABLE second_ids (i bigint);
INSERT INTO second_ids :values_clause;
EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id IN (select i from second_ids);
Aggregate (cost=225004.36..225004.37 rows=1 width=8) (actual time=3826.641..3826.641 rows=1 loops=1)
Buffers: shared hit=44249
-> Hash Semi Join (cost=5.50..215004.32 rows=4000019 width=0) (actual time=0.352..3338.601 rows=3998646 loops=1)
Hash Cond: (ids.id = second_ids.i)
Buffers: shared hit=44249
-> Seq Scan on ids (cost=0.00..144248.48 rows=10000048 width=8) (actual time=0.040..1069.006 rows=10000000 loops=1)
Buffers: shared hit=44248
-> Hash (cost=3.00..3.00 rows=200 width=8) (actual time=0.288..0.288 rows=200 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
Buffers: shared hit=1
-> Seq Scan on second_ids (cost=0.00..3.00 rows=200 width=8) (actual time=0.024..0.115 rows=200 loops=1)
Buffers: shared hit=1
Planning time: 0.413 ms
Execution time: 3826.752 ms
Hash Semi-Join without NOT NULL constraint on second table.