Any help on the below would be greatly appreciated. Two identical
queries on two nearly-identical setups (the slower query is happening
on a slower box, but not by anything close to the disparity between the
two: 167ms in analyze versus 70000 ms in analyze).
Table "public.status"
Column | Type | Modifiers
----------+---------+-----------
objectid | integer |
status | integer |
stamp | date |
type | integer |
id | integer |
Indexes: status_idx btree (id, objectid, status),
status_statustypestamp_idx btree (status, stamp, "type")
explain analyze select objectid from status where type = 54 and status
= 61 except select distinct s1.objectid from status s1, status s2 where
s1.type = 54 and s2.type = 54 and s1.objectid = s2.objectid and
s1.status = 61 and s2.status != 61 and s2.id > s1.id;
The slower one is 7.3.4, the quicker one is 7.3.2.
I don't see any significant differences between the two
postgresql.conf's other than the defaults are commented out (but not
replaced with anything) in one of the files (so I presume they're still
defaulted...? would that be correct? to whit:
< #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40
bytes
< #max_fsm_pages = 10000 # min 1000, fsm is free space
map, ~6 bytes
< #max_locks_per_transaction = 64 # min 10
< #wal_buffers = 8 # min 4, typically 8KB each
---
> max_fsm_relations = 1000 # min 10, fsm is free space map, ~40
bytes
> max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6
bytes
> max_locks_per_transaction = 64 # min 10
> wal_buffers = 8 # min 4, typically 8KB each
61c61
< sort_mem = 1024 # min 64, size in KB
---
> #sort_mem = 1024 # min 64, size in KB
74c74
------------------------------------------------------------------------
------------------------------------------------------------------------
-----------------------------------------
SetOp Except (cost=7439.38..7439.40 rows=1 width=16) (actual
time=167.72..168.59 rows=4 loops=1)
-> Sort (cost=7439.38..7439.39 rows=5 width=16) (actual
time=167.48..167.91 rows=1718 loops=1)
Sort Key: objectid
-> Append (cost=0.00..7439.32 rows=5 width=16) (actual
time=0.14..165.83 rows=1718 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..2138.53
rows=4 width=4) (actual time=0.14..7.66 rows=861 loops=1)
-> Index Scan using status_statustypestamp_idx on
status (cost=0.00..2138.53 rows=4 width=4) (actual time=0.13..6.93
rows=861 loops=1)
Index Cond: (status = 61)
Filter: ("type" = 54)
-> Subquery Scan "*SELECT* 2" (cost=5300.77..5300.80
rows=1 width=16) (actual time=155.38..157.30 rows=857 loops=1)
-> Unique (cost=5300.77..5300.80 rows=1
width=16) (actual time=155.37..156.50 rows=857 loops=1)
-> Sort (cost=5300.77..5300.79 rows=5
width=16) (actual time=155.37..155.75 rows=1580 loops=1)
Sort Key: s1.objectid
-> Hash Join (cost=2138.54..5300.73
rows=5 width=16) (actual time=2.39..153.72 rows=1580 loops=1)
Hash Cond: ("outer".objectid =
"inner".objectid)
Join Filter: ("outer".id >
"inner".id)
-> Seq Scan on status s2
(cost=0.00..3158.31 rows=735 width=8) (actual time=0.01..114.89
rows=50744 loops=1)
Filter: (("type" = 54) AND
(status <> 61))
-> Hash (cost=2138.53..2138.53
rows=4 width=8) (actual time=2.29..2.29 rows=0 loops=1)
-> Index Scan using
status_statustypestamp_idx on status s1 (cost=0.00..2138.53 rows=4
width=8) (actual time=0.03..1.77 rows=861 loops=1)
Index Cond: (status
= 61)
Filter: ("type" = 54)
------------------------------------------------------------------------
------------------------------------------------------------------------
-----------------------------------
SetOp Except (cost=2016.01..2016.03 rows=1 width=16) (actual
time=69635.62..69637.74 rows=960 loops=1)
-> Sort (cost=2016.01..2016.02 rows=2 width=16) (actual
time=69635.60..69636.18 rows=960 loops=1)
Sort Key: objectid
-> Append (cost=0.00..2016.00 rows=2 width=16) (actual
time=0.27..69633.61 rows=960 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..827.83
rows=1 width=4) (actual time=0.26..27.73 rows=960 loops=1)
-> Index Scan using status_statustypestamp_idx on
status (cost=0.00..827.83 rows=1 width=4) (actual time=0.26..25.69
rows=960 loops=1)
Index Cond: (status = 61)
Filter: ("type" = 54)
-> Subquery Scan "*SELECT* 2" (cost=1188.16..1188.17
rows=1 width=16) (actual time=69604.73..69604.73 rows=0 loops=1)
-> Unique (cost=1188.16..1188.17 rows=1
width=16) (actual time=69604.73..69604.73 rows=0 loops=1)
-> Sort (cost=1188.16..1188.17 rows=1
width=16) (actual time=69604.72..69604.72 rows=0 loops=1)
Sort Key: s1.objectid
-> Nested Loop (cost=0.00..1188.15
rows=1 width=16) (actual time=69604.62..69604.62 rows=0 loops=1)
-> Index Scan using
status_statustypestamp_idx on status s1 (cost=0.00..827.83 rows=1
width=8) (actual time=0.06..59.51 rows=960 loops=1)
Index Cond: (status = 61)
Filter: ("type" = 54)
-> Index Scan using status_idx
on status s2 (cost=0.00..304.33 rows=1 width=8) (actual
time=72.42..72.42 rows=0 loops=960)
Index Cond: ((s2.id >
"outer".id) AND ("outer".objectid = s2.objectid))
Filter: (("type" = 54) AND
((status !) = 61))
Total runtime: 69639.04 msec
-kaolin fire
