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