Why is Postgres crawling a huge table instead of using my index?

Question

I noticed that one of my SQL queries is much slower than I expected, and it turns out that the query planner comes up with a plan that seems very bad to me. My query looks like this:

select A.style, count(B.x is null) as missing, count(*) as total
  from A left join B using (id, type)
  where A.country_code in ('US', 'DE', 'ES')
  group by A.country_code, A.style
  order by A.country_code, total

B has an index (type, id), and A has an index (country_code, style). A is much smaller than B: 250K lines versus 100M in B.

So, I expected the query plan to look something like this:

• Use index on A to select only those rows with matching country_code
• Left join with B to find a suitable row (if any) based on index (type, id)
• Group things according to country_codeandstyle
• Add counters

, - B, A. , ; ? :

 Sort  (cost=14283513.27..14283513.70 rows=171 width=595)
   Sort Key: a.country_code, (count(*))
   ->  HashAggregate  (cost=14283505.22..14283506.93 rows=171 width=595)
         ->  Hash Right Join  (cost=8973.71..14282810.03 rows=55615 width=595)
               Hash Cond: ((b.type = a.type) AND (b.id = a.id))
               ->  Seq Scan on b (cost=0.00..9076222.44 rows=129937844 width=579)
               ->  Hash  (cost=8139.49..8139.49 rows=55615 width=28)
                     ->  Bitmap Heap Scan on a  (cost=1798.67..8139.49 rows=55615 width=28)
                           Recheck Cond: ((country_code = ANY ('{US,DE,ES}'::bpchar[])))
                           ->  Bitmap Index Scan on a_country_code_type_idx  (cost=0.00..1784.76 rows=55615 width=0)
                                 Index Cond: ((country_code = ANY ('{US,DE,ES}'::bpchar[])))

: , SET ENABLE_SEQSCAN TO OFF;, . , , , .

Answer 1

, , :

• , .
• , Postgres .

:

• PostgreSQL

Answer 2

, . , 55k . . - .

:

• .
• .