Re: Help with tuning this query (with explain analyze finally)

Josh,

I did everything you said and my query does perform a bit better. I've been
getting speeds from 203 to 219 to 234 milliseconds now. I tried increasing
the work mem and the effective cache size from the values you provided, but
I didn't see any more improvement. I've tried to looking into setting the
shared buffers for Windows XP, but I'm not sure how to do it. I'm looking
in the manual at:
http://www.postgresql.org/docs/8.0/interactive/kernel-resources.html#SYSVIPC-PARAMETERS

It doesn't mention windows at all. Does anyone have any ideas on have to
fix this?

Here is the new explain analyze.

Sort (cost=1996.21..1996.26 rows=17 width=165) (actual
time=297.000..297.000 rows=39 loops=1)
Sort Key: ss.date
-> Merge Right Join (cost=1951.26..1995.87 rows=17 width=165) (actual
time=297.000..297.000 rows=39 loops=1)
Merge Cond: ("outer".id = "inner".driver_id)
-> Index Scan using driver_pkey on driver d (cost=0.00..42.16
rows=922 width=43) (actual time=0.000..0.000 rows=922 loops=1)
-> Sort (cost=1951.26..1951.30 rows=17 width=122) (actual
time=297.000..297.000 rows=39 loops=1)
Sort Key: s.driver_id
-> Hash Join (cost=586.48..1950.91 rows=17 width=122)
(actual time=219.000..297.000 rows=39 loops=1)
Hash Cond: ("outer".carrier_code_id = "inner".id)
-> Merge Join (cost=571.97..1931.95 rows=830 width=87)
(actual time=219.000..297.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..2701.26 rows=60307
width=66) (actual time=0.000..62.000 rows=27711 loops=1)
Filter: (is_purged = false)
-> Sort (cost=571.97..576.38 rows=1766 width=21)
(actual time=125.000..156.000 rows=6902 loops=1)
Sort Key: ss.id
-> Hash Join (cost=1.11..476.72 rows=1766
width=21) (actual time=0.000..93.000 rows=6902 loops=1)
Hash Cond: ("outer".release_code_id =
"inner".id)
-> Index Scan Backward using
current_status_date_idx on shipment_status ss (cost=0.00..387.35 rows=14122
width=21) (actual time=0.000..16.000 rows=14925 loops=1)
Index Cond: (date >=
(('now'::text)::date - 31))
-> Hash (cost=1.10..1.10 rows=1
width=4) (actual time=0.000..0.000 rows=0 loops=1)
-> Seq Scan on release_code rc
(cost=0.00..1.10 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)
Filter: ((number)::text =
'9'::text)
-> Hash (cost=14.51..14.51 rows=2 width=35) (actual
time=0.000..0.000 rows=0 loops=1)
-> Nested Loop (cost=4.92..14.51 rows=2
width=35) (actual time=0.000..0.000 rows=2 loops=1)
-> Index Scan using person_pkey on person p
(cost=0.00..5.73 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)
Index Cond: (id = 355)
-> Hash Join (cost=4.92..8.75 rows=2
width=39) (actual time=0.000..0.000 rows=2 loops=1)
Hash Cond: ("outer".id =
"inner".carrier_id)
-> Seq Scan on carrier c
(cost=0.00..3.54 rows=54 width=4) (actual time=0.000..0.000 rows=54 loops=1)
-> Hash (cost=4.92..4.92 rows=2
width=43) (actual time=0.000..0.000 rows=0 loops=1)
-> Hash Join (cost=3.04..4.92
rows=2 width=43) (actual time=0.000..0.000 rows=2 loops=1)
Hash Cond:
("outer".carrier_id = "inner".carrier_id)
-> Seq Scan on
carrier_code cc (cost=0.00..1.57 rows=57 width=35) (actual
time=0.000..0.000 rows=57 loops=1)
-> Hash (cost=3.04..3.04
rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=1)
-> Index Scan using
carrier_to_person_person_id_idx on carrier_to_person ctp (cost=0.00..3.04
rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)
Index Cond:
(355 = person_id)
Total runtime: 297.000 ms