Re: Help with tuning this query (more musings)

Ken Egervari wrote:
>
> 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