Understanding postgres reason for picking this query plan I ran a query:

select * from orderline where orderid = 12345 and status &gt; 0


that gave me the following plan:

Bitmap Heap Scan on orderline  (cost=286370.00..531410.78 rows=76372 width=155)
  Recheck Cond: ((orderid = 12081976) AND (itemstatus &gt; 0))
  -&gt;  BitmapAnd  (cost=286370.00..286370.00 rows=76372 width=0)
        -&gt;  Bitmap Index Scan on orderline_orderid_index  (cost=0.00..4239.65 rows=229117 width=0)
              Index Cond: (orderid = 12081976)
        -&gt;  Bitmap Index Scan on orderline_status  (cost=0.00..282091.91 rows=15274484 width=0)
              Index Cond: (status &gt; 0)


I wanted to understand why postgres decided to run two separate index scans rather than the filter on the forign key (orderline_orderid - which returns about 5 rows out of millions) then filtering status from these results.

How can I work out what I've set up wrong here? (I'm assuming index fragmentation or maybe status shouldn't have an index?)

EDIT: I tried some of the things in the comments and the discovered the following:

I thought the cause of the issue was the index as I removed it and the problem went away:

Bitmap Heap Scan on orderline  (cost=4258.75..597209.93 rows=76372 width=155)
  Recheck Cond: (orderid = 12081976)
  Filter: (status &gt; 0)
  -&gt;  Bitmap Index Scan on orderline_orderid_index  (cost=0.00..4239.65 rows=229117 width=0)
        Index Cond: (orderid = 12081976)


However, I removed and added then index and then ran analyse and the the follow occured:

Index Scan using orderline_orderid_index on orderline  (cost=0.00..14.87 rows=4 width=63)
  Index Cond: (orderid = 12085242)
  Filter: (status &gt; 0)


So is this because I rebuilt the index, or because I ran analyse?
It looks like Postgres had no idea how your values are distributed. It guessed that there's 229117 rows with orderid = 12081976 when it really was 5 of them. It has then chosen a plan which was optimal for 229117 rows.

When you ran analyze it was able to generate and save statistics about distribution of values in your table. And then it knew that there's about 4 rows with this orderid. And this allowed it to pick a better plan.

So it is analyze that caused this. Remember to run it after large changes to database data — for example after importing from external source.

Also — when you analyze your plans try to use explain analyze [query]; instead of just explain [query];. You can paste it to explain.depesz.com for easier to understand format.