comments

I have indices on orfs.contig_id and contigs.assembly_id. It only seems to be using the latter for some reason.

initial results.sql

explain analyze
 SELECT o.id
 FROM orfs AS o JOIN contigs AS c ON o.contig_id = c.id
 WHERE c.assembly_id = 505


Hash Join  (cost=7491.19..1846159.16 rows=22595 width=4) (actual time=102305.719..230602.594 rows=13572 loops=1)
  Hash Cond: (o.contig_id = c.id)
  ->  Seq Scan on orfs o  (cost=0.00..1826636.79 rows=7975597 width=8) (actual time=0.038..224557.753 rows=7975597 loops=1)
  ->  Hash  (cost=7477.58..7477.58 rows=3889 width=4) (actual time=8.412..8.412 rows=3989 loops=1)
        ->  Index Scan using index_contigs_on_assembly_id on contigs c  (cost=0.00..7477.58 rows=3889 width=4) (actual time=0.022..5.534 rows=3989 loops=1)
              Index Cond: (assembly_id = 505)
Total runtime: 230610.904 ms

try 2 - after analyze

Hash Join  (cost=8933.48..1849407.83 rows=27239 width=4) (actual time=38047.769..128250.614 rows=13572 loops=1)
  Hash Cond: (o.contig_id = c.id)
  ->  Seq Scan on orfs o  (cost=0.00..1826509.56 rows=7933186 width=8) (actual time=0.033..121485.734 rows=7975597 loops=1)
  ->  Hash  (cost=8916.72..8916.72 rows=4787 width=4) (actual time=6.441..6.441 rows=3989 loops=1)
        ->  Index Scan using index_contigs_on_assembly_id on contigs c  (cost=0.00..8916.72 rows=4787 width=4) (actual time=0.013..3.670 rows=3989 loops=1)
              Index Cond: (assembly_id = 505)
Total runtime: 128259.525 ms

try 3 - after unsetting cpu costs

Hash Join  (cost=9038.99..1960768.62 rows=27239 width=4) (actual time=20299.675..50348.400 rows=13572 loops=1)
  Hash Cond: (o.contig_id = c.id)
  ->  Seq Scan on orfs o  (cost=0.00..1882041.86 rows=7933186 width=8) (actual time=0.028..42191.211 rows=7975597 loops=1)
  ->  Hash  (cost=8979.15..8979.15 rows=4787 width=4) (actual time=1836.093..1836.093 rows=3989 loops=1)
        ->  Index Scan using index_contigs_on_assembly_id on contigs c  (cost=0.00..8979.15 rows=4787 width=4) (actual time=749.715..1831.232 rows=3989 loops=1)
              Index Cond: (assembly_id = 505)
Total runtime: 50357.673 ms

try 4 - after setting cpu_tuple_cost to 0.02

Hash Join  (cost=9134.73..2040468.61 rows=27239 width=4) (actual time=4654.140..15993.432 rows=13572 loops=1)
  Hash Cond: (o.contig_id = c.id)
  ->  Seq Scan on orfs o  (cost=0.00..1961373.72 rows=7933186 width=8) (actual time=0.020..10270.618 rows=7975597 loops=1)
  ->  Hash  (cost=9027.02..9027.02 rows=4787 width=4) (actual time=12.130..12.130 rows=3989 loops=1)
        ->  Index Scan using index_contigs_on_assembly_id on contigs c  (cost=0.00..9027.02 rows=4787 width=4) (actual time=0.051..9.119 rows=3989 loops=1)
              Index Cond: (assembly_id = 505)
Total runtime: 16001.684 ms

try 5 - after setting cpu_tuple_cost to 0.2

Nested Loop  (cost=0.00..3207298.47 rows=27239 width=4) (actual time=51.836..336.643 rows=13572 loops=1)
  ->  Index Scan using index_contigs_on_assembly_id on contigs c  (cost=0.00..9888.68 rows=4787 width=4) (actual time=0.050..11.188 rows=3989 loops=1)
        Index Cond: (assembly_id = 505)
  ->  Index Scan using index_orfs_on_contig_id on orfs o  (cost=0.00..609.01 rows=291 width=8) (actual time=0.069..0.076 rows=3 loops=3989)
        Index Cond: (o.contig_id = c.id)
Total runtime: 345.000 ms