Hash IN Join (cost=676.15..1943.11 rows=14 width=91) (actual time=250.000..328.000 rows=39 loops=1)
Hash Cond: ("outer".carrier_code_id = "inner".id)
-> Merge Join (cost=661.65..1926.51 rows=392 width=91) (actual time=250.000..328.000 rows=310 loops=1)
Merge Cond: ("outer".current_status_id = "inner".id)
-> Index Scan using shipment_current_status_id_idx on shipment s (cost=0.00..2702.56 rows=27257 width=66) (actual time=0.000..110.000 rows=27711 loops=1)
Filter: ((current_status_id IS NOT NULL) AND (is_purged = false))
There's a feature in PG called partial indexes - see CREATE INDEX reference for details. Basically you can do something like:
CREATE INDEX foo_idx ON shipment (carrier_code_id) WHERE current_status_id IS NOT NULL AND is_purged = FALSE;
Something similar may be a win here, although the above index might not be quite right - sorry, bit tired at moment.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
