identical queries performing differently? - Mailing list pgsql-admin

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