Re: 8.2.4 serious slowdown

I just upgraded my database server from 8.0.1 to 8.2.4
Most things went very well, but I have a couple of queries that really slowed down with the new server.
On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
(I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).

The data is exactly the same on the 2 servers.

To test for hardware differences, I loaded 8.0.11 onto a test server restored the database and ran the query.
It took about 3 seconds. I then uninstalled postgresql and installed version 8.2.4 and restored the database
and the query took about 60 seconds.

On the 8.2.4 the CPU usage (as seen from top) goes up to about 97% for most of the 60 seconds of query.
On 8.0.1, it didn't.

I have the explain from both databases, if someone could help me walk through this, I would much appreciate it.

----------------------------------------------------------------------------------------------------------------------
Explain 8.0.1 Fast query

Subquery Scan assemblycanbuild (cost=8495.27..8509.34 rows=13 width=36) (actual time=3585.026..3753.339 rows=83 loops=1)
-> GroupAggregate (cost=8495.27..8509.21 rows=13 width=32) (actual time=3585.015..3752.729 rows=83 loops=1)
-> Subquery Scan assembliesstockbatchpriorexpected (cost=8495.27..8508.30 rows=13 width=32) (actual time=3584.912..3729.404 rows=3684 loops=1)
-> GroupAggregate (cost=8495.27..8508.17 rows=13 width=112) (actual time=3584.900..3699.779 rows=3684 loops=1)
-> Sort (cost=8495.27..8496.23 rows=382 width=112) (actual time=3584.836..3613.432 rows=7400 loops=1)
Sort Key: a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.quantity, a.stock, a.prioruse, a.units, a.qtyperunit
-> Hash Left Join (cost=8220.13..8478.89 rows=382 width=112) (actual time=2902.740..3407.342 rows=7400 loops=1)
Hash Cond: ("outer".partid = "inner".partid)
-> Subquery Scan a (cost=6877.75..6920.40 rows=125 width=88) (actual time=2700.471..3140.321 rows=3684 loops=1)
-> GroupAggregate (cost=6877.75..6919.15 rows=125 width=85) (actual time=2700.456..3106.694 rows=3684 loops=1)
-> Sort (cost=6877.75..6880.86 rows=1245 width=85) (actual time=2700.414..2839.777 rows=36876 loops=1)
Sort Key: a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.quantity, a.stock, a.units, a.qtyperunit, a.leadfree
-> Hash Left Join (cost=6582.30..6813.74 rows=1245 width=85) (actual time=1458.482..1887.078 rows=36876 loops=1)
Hash Cond: (("outer".partid = "inner".partid) AND ("outer".leadfree = "inner".leadfree))
Join Filter: ((COALESCE("outer".ownerid, 1) = 1) AND (("outer".duedate > "inner".duedate) OR (("outer".duedate = "inner".duedate) AND ("outer".assembliesbatchid > "inner".assembliesbatchid))))
-> Subquery Scan a (cost=6012.11..6068.13 rows=1245 width=81) (actual time=1252.814..1340.992 rows=3684 loops=1)
-> GroupAggregate (cost=6012.11..6055.68 rows=1245 width=82) (actual time=1252.799..1307.969 rows=3684 loops=1)
-> Sort (cost=6012.11..6015.22 rows=1245 width=82) (actual time=1252.759..1265.317 rows=3685 loops=1)
Sort Key: d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname, c.ownerid, e.partid, COALESCE(c.stock, 0::bigint), d.units, e.quantity, a.leadfree
-> Merge Left Join (cost=5816.85..5948.10 rows=1245 width=82) (actual time=1169.837..1220.895 rows=3685 loops=1)
Merge Cond: (("outer".batchid = "inner".refid) AND ("outer".partid = "inner".partid))
Filter: (COALESCE("inner".commited, false) = false)
-> Sort (cost=2382.11..2385.22 rows=1245 width=86) (actual time=682.055..694.675 rows=3684 loops=1)
Sort Key: d.batchid, e.partid
-> Hash Left Join (cost=737.64..2318.10 rows=1245 width=86) (actual time=250.089..665.021 rows=3684 loops=1)
Hash Cond: ("outer".partid = "inner".partid)
Join Filter: leadcompcheck_ab("outer".leadfree, "inner".leadstateid)
-> Merge Right Join (cost=722.62..2296.73 rows=1245 width=74) (actual time=169.106..506.307 rows=3684 loops=1)
Merge Cond: (("outer".partid = "inner".partid) AND ("outer".assemblyid = "inner".assemblyid))
-> Index Scan using idx_u_assidpartid on partsassembly b (cost=0.00..1396.01 rows=34286 width=16) (actual time=0.147..151.393 rows=34286 loops=1)
-> Sort (cost=722.62..725.74 rows=1245 width=66) (actual time=168.091..180.485 rows=3684 loops=1)
Sort Key: e.partid, a.assemblyid
-> Hash Join (cost=71.73..658.62 rows=1245 width=66) (actual time=12.252..148.296 rows=3684 loops=1)
Hash Cond: ("outer".assemblyid = "inner".assemblyid)
-> Hash Join (cost=54.68..622.89 rows=1245 width=32) (actual time=6.377..111.172 rows=3684 loops=1)
Hash Cond: ("outer".assembliesbatchid = "inner".assembliesbatchid)
-> Seq Scan on allocatedassemblies e (cost=0.00..460.93 rows=18967 width=12) (actual time=0.032..51.827 rows=11332 loops=1)
Filter: ((- quantity) <> 0)
-> Hash (cost=54.43..54.43 rows=98 width=24) (actual time=1.364..1.364 rows=0 loops=1)
-> Index Scan using fki_assembliesbatch_assembliesbatchstatus_id, fki_assembliesbatch_assembliesbatchstatus_id, fki_assembliesbatch_assembliesbatchstatus_id, fki_assembliesbatch_assembliesbatchstatus_id on assembliesbatch d (cost=0.00..54.43 rows=98 width=24) (actual time=0.105..0.985 rows=99 loops=1)
Index Cond: ((assembliesbatchstatusid = 1) OR (assembliesbatchstatusid = 2) OR (assembliesbatchstatusid = 4) OR (assembliesbatchstatusid = 7))
-> Hash (cost=15.24..15.24 rows=724 width=38) (actual time=5.844..5.844 rows=0 loops=1)
-> Seq Scan on assemblies a (cost=0.00..15.24 rows=724 width=38) (actual time=0.030..3.149 rows=724 loops=1)
-> Hash (cost=15.00..15.00 rows=5 width=20) (actual time=80.500..80.500 rows=0 loops=1)
-> Function Scan on stockperowner_lead_ab c (cost=0.00..15.00 rows=5 width=20) (actual time=67.238..74.347 rows=1694 loops=1)
Filter: (ownerid = 1)
-> Sort (cost=3434.74..3498.75 rows=25605 width=9) (actual time=313.138..403.616 rows=25267 loops=1)
Sort Key: f.refid, f.partid
-> Seq Scan on stocklog f (cost=0.00..1559.92 rows=25605 width=9) (actual time=0.066..146.728 rows=25267 loops=1)
Filter: (transtypeid = 3)
-> Hash (cost=563.93..563.93 rows=1251 width=21) (actual time=205.583..205.583 rows=0 loops=1)
-> Hash Join (cost=71.73..563.93 rows=1251 width=21) (actual time=11.923..190.945 rows=3851 loops=1)
Hash Cond: ("outer".assemblyid = "inner".assemblyid)
-> Hash Join (cost=54.68..528.12 rows=1251 width=24) (actual time=6.159..155.650 rows=3851 loops=1)
Hash Cond: ("outer".assembliesbatchid = "inner".assembliesbatchid)
-> Seq Scan on allocatedassemblies b (cost=0.00..365.62 rows=19062 width=12) (actual time=0.029..71.287 rows=19062 loops=1)
-> Hash (cost=54.43..54.43 rows=98 width=16) (actual time=1.287..1.287 rows=0 loops=1)
-> Index Scan using fki_assembliesbatch_assembliesbatchstatus_id, fki_assembliesbatch_assembliesbatchstatus_id, fki_assembliesbatch_assembliesbatchstatus_id, fki_assembliesbatch_assembliesbatchstatus_id on assembliesbatch c (cost=0.00..54.43 rows=98 width=16) (actual time=0.090..0.921 rows=99 loops=1)
Index Cond: ((assembliesbatchstatusid = 1) OR (assembliesbatchstatusid = 2) OR (assembliesbatchstatusid = 4) OR (assembliesbatchstatusid = 7))
-> Hash (cost=15.24..15.24 rows=724 width=5) (actual time=5.733..5.733 rows=0 loops=1)
-> Seq Scan on assemblies q (cost=0.00..15.24 rows=724 width=5) (actual time=0.040..3.081 rows=724 loops=1)
-> Hash (cost=1332.57..1332.57 rows=3924 width=28) (actual time=202.198..202.198 rows=0 loops=1)
-> Hash Join (cost=592.15..1332.57 rows=3924 width=28) (actual time=66.119..199.853 rows=593 loops=1)
Hash Cond: ("outer".pnid = "inner".pnid)
-> Hash Join (cost=377.64..1019.94 rows=3925 width=32) (actual time=11.525..139.401 rows=593 loops=1)
Hash Cond: ("outer".poid = "inner".poid)
-> Seq Scan on poparts e (cost=0.00..476.60 rows=16860 width=32) (actual time=0.037..65.660 rows=16860 loops=1)
-> Hash (cost=373.59..373.59 rows=1620 width=8) (actual time=11.348..11.348 rows=0 loops=1)
-> Seq Scan on pos f (cost=0.00..373.59 rows=1620 width=8) (actual time=0.124..10.621 rows=183 loops=1)
Filter: ((postatusid >= 20) AND (postatusid <= 59) AND (isrfq = false))
-> Hash (cost=197.01..197.01 rows=7001 width=4) (actual time=54.561..54.561 rows=0 loops=1)
-> Seq Scan on manufacturerpartpn g (cost=0.00..197.01 rows=7001 width=4) (actual time=0.035..29.047 rows=7001 loops=1)
Total runtime: 3763.256 ms

---------------------------------------------------------------------------------------------------------------------

8.2.4 Slow query
GroupAggregate (cost=5944.26..5944.50 rows=1 width=32) (actual time=608067.502..608144.235 rows=83 loops=1)
-> GroupAggregate (cost=5944.26..5944.41 rows=1 width=112) (actual time=608067.439..608127.305 rows=3684 loops=1)
-> Sort (cost=5944.26..5944.27 rows=3 width=112) (actual time=608067.381..608083.775 rows=7400 loops=1)
Sort Key: a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.quantity, a.stock, a.prioruse, a.units, a.qtyperunit
-> Nested Loop Left Join (cost=5311.54..5944.24 rows=3 width=112) (actual time=341040.765..607912.624 rows=7400 loops=1)
Join Filter: (e.partid = a.partid)
-> GroupAggregate (cost=4689.90..4689.96 rows=1 width=85) (actual time=340891.895..341154.807 rows=3684 loops=1)
-> Sort (cost=4689.90..4689.91 rows=1 width=85) (actual time=340891.872..340989.892 rows=36876 loops=1)
Sort Key: a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.quantity, a.stock, a.units, a.qtyperunit, a.leadfree
-> Nested Loop Left Join (cost=4224.98..4689.89 rows=1 width=85) (actual time=22886.336..340100.378 rows=36876 loops=1)
Join Filter: ((a.partid = b.partid) AND (COALESCE(a.ownerid, 1) = 1) AND (a.leadfree = q.leadfree) AND ((a.duedate > c.duedate) OR ((a.duedate = c.duedate) AND (a.assembliesbatchid > c.assembliesbatchid))))
-> GroupAggregate (cost=4127.29..4127.34 rows=1 width=82) (actual time=22801.528..22859.419 rows=3684 loops=1)
-> Sort (cost=4127.29..4127.30 rows=1 width=82) (actual time=22801.498..22812.312 rows=3685 loops=1)
Sort Key: d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname, c.ownerid, e.partid, COALESCE(c.stock, 0::bigint), d.units, e.quantity, a.leadfree
-> Nested Loop Left Join (cost=3984.15..4127.28 rows=1 width=82) (actual time=360.261..22766.654 rows=3685 loops=1)
-> Nested Loop Left Join (cost=3984.15..4126.99 rows=1 width=74) (actual time=360.168..22680.414 rows=3685 loops=1)
Join Filter: ((c.partid = e.partid) AND leadcompcheck_ab(a.leadfree, c.leadstateid))
-> Nested Loop (cost=3984.15..4111.92 rows=1 width=62) (actual time=319.721..411.494 rows=3685 loops=1)
-> Merge Left Join (cost=3984.15..4111.60 rows=1 width=28) (actual time=319.642..348.285 rows=3685 loops=1)
Merge Cond: ((d.batchid = f.refid) AND (e.partid = f.partid))
Filter: (NOT COALESCE(f.commited, false))
-> Sort (cost=664.36..667.47 rows=1244 width=32) (actual time=68.579..75.827 rows=3684 loops=1)
Sort Key: d.batchid, e.partid
-> Hash Join (cost=71.92..600.42 rows=1244 width=32) (actual time=3.199..52.985 rows=3684 loops=1)
Hash Cond: (e.assembliesbatchid = d.assembliesbatchid)
-> Seq Scan on allocatedassemblies e (cost=0.00..444.93 rows=18967 width=12) (actual time=0.060..25.590 rows=11332 loops=1)
Filter: ((- quantity) <> 0)
-> Hash (cost=70.70..70.70 rows=98 width=24) (actual time=0.672..0.672 rows=99 loops=1)
-> Bitmap Heap Scan on assembliesbatch d (cost=17.75..70.70 rows=98 width=24) (actual time=0.149..0.453 rows=99 loops=1)
Recheck Cond: (assembliesbatchstatusid = ANY ('{1,2,4,7}'::integer[]))
-> Bitmap Index Scan on fki_assembliesbatch_assembliesbatchstatus_id (cost=0.00..17.72 rows=98 width=0) (actual time=0.121..0.121 rows=99 loops=1)
Index Cond: (assembliesbatchstatusid = ANY ('{1,2,4,7}'::integer[]))
-> Sort (cost=3319.79..3382.16 rows=24951 width=9) (actual time=160.006..210.204 rows=25267 loops=1)
Sort Key: f.refid, f.partid
-> Bitmap Heap Scan on stocklog f (cost=417.63..1497.51 rows=24951 width=9) (actual time=5.599..63.420 rows=25267 loops=1)
Recheck Cond: (transtypeid = 3)
-> Bitmap Index Scan on targetidsl (cost=0.00..411.39 rows=24951 width=0) (actual time=5.379..5.379 rows=25267 loops=1)
Index Cond: (transtypeid = 3)
-> Index Scan using assemblies_pkey on assemblies a (cost=0.00..0.31 rows=1 width=38) (actual time=0.007..0.009 rows=1 loops=3685)
Index Cond: (d.assemblyid = a.assemblyid)
-> Function Scan on stockperowner_lead_ab c (cost=0.00..15.00 rows=5 width=20) (actual time=0.012..3.162 rows=1694 loops=3685)
Filter: (ownerid = 1)
-> Index Scan using idx_u_assidpartid on partsassembly b (cost=0.00..0.27 rows=1 width=16) (actual time=0.010..0.012 rows=1 loops=3685)
Index Cond: ((e.partid = b.partid) AND (b.assemblyid = a.assemblyid))
-> Hash Join (cost=97.69..531.29 rows=1250 width=21) (actual time=2.395..78.855 rows=3851 loops=3684)
Hash Cond: (b.assembliesbatchid = c.assembliesbatchid)
-> Seq Scan on allocatedassemblies b (cost=0.00..349.62 rows=19062 width=12) (actual time=0.009..35.493 rows=19062 loops=3684)
-> Hash (cost=96.47..96.47 rows=98 width=13) (actual time=3.796..3.796 rows=99 loops=1)
-> Hash Join (cost=42.14..96.47 rows=98 width=13) (actual time=2.939..3.596 rows=99 loops=1)
Hash Cond: (c.assemblyid = q.assemblyid)
-> Bitmap Heap Scan on assembliesbatch c (cost=17.85..70.83 rows=98 width=16) (actual time=0.137..0.397 rows=99 loops=1)
Recheck Cond: ((assembliesbatchstatusid = 1) OR (assembliesbatchstatusid = 2) OR (assembliesbatchstatusid = 4) OR (assembliesbatchstatusid = 7))
-> BitmapOr (cost=17.85..17.85 rows=99 width=0) (actual time=0.111..0.111 rows=0 loops=1)
-> Bitmap Index Scan on fki_assembliesbatch_assembliesbatchstatus_id (cost=0.00..4.85 rows=80 width=0) (actual time=0.066..0.066 rows=80 loops=1)
Index Cond: (assembliesbatchstatusid = 1)
-> Bitmap Index Scan on fki_assembliesbatch_assembliesbatchstatus_id (cost=0.00..4.26 rows=1 width=0) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: (assembliesbatchstatusid = 2)
-> Bitmap Index Scan on fki_assembliesbatch_assembliesbatchstatus_id (cost=0.00..4.27 rows=3 width=0) (actual time=0.006..0.006 rows=3 loops=1)
Index Cond: (assembliesbatchstatusid = 4)
-> Bitmap Index Scan on fki_assembliesbatch_assembliesbatchstatus_id (cost=0.00..4.37 rows=16 width=0) (actual time=0.022..0.022 rows=16 loops=1)
Index Cond: (assembliesbatchstatusid = 7)
-> Hash (cost=15.24..15.24 rows=724 width=5) (actual time=2.785..2.785 rows=724 loops=1)
-> Seq Scan on assemblies q (cost=0.00..15.24 rows=724 width=5) (actual time=0.011..1.356 rows=724 loops=1)
-> Hash Join (cost=621.63..1206.10 rows=3854 width=28) (actual time=0.074..71.265 rows=593 loops=3684)
Hash Cond: (e.pnid = g.pnid)
-> Hash Join (cost=337.11..839.61 rows=3860 width=32) (actual time=0.057..68.467 rows=593 loops=3684)
Hash Cond: (e.poid = f.poid)
-> Seq Scan on poparts e (cost=0.00..379.60 rows=16860 width=32) (actual time=0.008..34.510 rows=16860 loops=3684)
-> Hash (cost=317.17..317.17 rows=1595 width=8) (actual time=7.266..7.266 rows=183 loops=1)
-> Bitmap Heap Scan on pos f (cost=54.06..317.17 rows=1595 width=8) (actual time=1.519..6.843 rows=183 loops=1)
Recheck Cond: ((postatusid >= 20) AND (postatusid <= 59))
Filter: (NOT isrfq)
-> Bitmap Index Scan on postatusidpo (cost=0.00..53.66 rows=2541 width=0) (actual time=1.418..1.418 rows=2700 loops=1)
Index Cond: ((postatusid >= 20) AND (postatusid <= 59))
-> Hash (cost=197.01..197.01 rows=7001 width=4) (actual time=42.248..42.248 rows=7001 loops=1)
-> Seq Scan on manufacturerpartpn g (cost=0.00..197.01 rows=7001 width=4) (actual time=0.030..19.935 rows=7001 loops=1)
Total runtime: 608146.760 ms

Here are all of the data structures involved in this view.
Query Ran: select * from assemblycanbuild

CREATE OR REPLACE VIEW assemblycanbuild AS
SELECT assembliesbatchid,
CASE
WHEN min(
CASE
WHEN (stock::double precision - prioruse - quantity::double precision) >= 0::double precision THEN 100000000::double precision
WHEN COALESCE(qtyperunit, 0::double precision) = 0::double precision OR (stock::double precision - prioruse) < 0::double precision THEN 0::double precision
ELSE trunc((stock::double precision - prioruse) / qtyperunit)
END) = 100000000::double precision THEN 'All'::character varying
ELSE min(
CASE
WHEN COALESCE(qtyperunit, 0::double precision) = 0::double precision OR (stock::double precision - prioruse) < 0::double precision THEN 0::double precision
ELSE trunc((stock::double precision - prioruse) / qtyperunit)
END)::character varying
END AS canbuild
FROM assembliesstockbatchpriorexpected
WHERE quantity <> 0
GROUP BY assembliesbatchid;

CREATE OR REPLACE VIEW assembliesstockbatchpriorexpected AS
SELECT a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.units, a.quantity, a.stock, a.prioruse, COALESCE(sum(
CASE
WHEN COALESCE(e.promisedby::timestamp without time zone::timestamp with time zone, e.requestedby::timestamp without time zone::timestamp with time zone,
CASE
WHEN e.deliverywks IS NULL THEN f.issuedate
ELSE NULL::date
END::timestamp without time zone::timestamp with time zone,
CASE
WHEN e.deliverywks <> -1 THEN (f.issuedate + e.deliverywks * 7)::timestamp without time zone::timestamp with time zone
ELSE a.duedate + '1 day'::interval
END) <= a.duedate THEN COALESCE(e.quantity, 0) - COALESCE(e.deliveredsum, 0)
ELSE NULL::integer
END), 0::bigint) AS expectedbefore, a.qtyperunit
FROM assembliesstockbatchprioruse a
LEFT JOIN (pos f
JOIN poparts e ON f.poid = e.poid AND f.postatusid >= 20 AND f.postatusid <= 59 AND f.isrfq = false
JOIN manufacturerpartpn g ON g.pnid = e.pnid) ON e.partid = a.partid
GROUP BY a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.quantity, a.stock, a.prioruse, a.units, a.qtyperunit;

CREATE OR REPLACE VIEW assembliesstockbatchprioruse AS
SELECT a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.units, a.quantity, a.stock, COALESCE(sum(- b.quantity)::double precision, 0::double precision) AS prioruse, a.qtyperunit, a.leadfree
FROM assembliesstockbatch a
LEFT JOIN (allocatedassemblies b
JOIN assembliesbatch c ON b.assembliesbatchid = c.assembliesbatchid AND (c.assembliesbatchstatusid = 1 OR c.assembliesbatchstatusid = 2 OR c.assembliesbatchstatusid = 4 OR c.assembliesbatchstatusid = 7)
JOIN assemblies q ON q.assemblyid = c.assemblyid) ON a.partid = b.partid AND COALESCE(a.ownerid, 1) = 1 AND a.leadfree = q.leadfree AND (a.duedate > c.duedate OR a.duedate = c.duedate AND a.assembliesbatchid > c.assembliesbatchid)
GROUP BY a.assembliesbatchid, a.duedate, a.assemblyid, a.assemblyname, a.ownerid, a.partid, a.quantity, a.stock, a.units, a.qtyperunit, a.leadfree;

CREATE OR REPLACE VIEW assembliesstockbatch AS
SELECT d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname, c.ownerid, e.partid, d.units, - e.quantity AS quantity, COALESCE(c.stock, 0::bigint) AS stock, max(b.quantity) AS qtyperunit, a.leadfree
FROM assemblies a
JOIN assembliesbatch d ON d.assemblyid = a.assemblyid
JOIN allocatedassemblies e ON e.assembliesbatchid = d.assembliesbatchid
LEFT JOIN partsassembly b ON b.assemblyid = a.assemblyid AND e.partid = b.partid
LEFT JOIN stockperowner_lead_ab() c(partid, ownerid, stock, leadstateid) ON c.partid = e.partid AND c.ownerid = 1 AND leadcompcheck_ab(a.leadfree, c.leadstateid)
LEFT JOIN stocklog f ON f.refid = d.batchid AND f.transtypeid = 3 AND f.partid = e.partid
WHERE (d.assembliesbatchstatusid = ANY (ARRAY[1, 2, 4, 7])) AND COALESCE(f.commited, false) = false
GROUP BY d.assembliesbatchid, d.duedate, a.assemblyid, a.assemblyname, c.ownerid, e.partid, COALESCE(c.stock, 0::bigint), d.units, e.quantity, a.leadfree;

CREATE OR REPLACE FUNCTION stockperowner_lead_ab()
RETURNS SETOF stockperowner AS
$BODY$
declare
row stockperowner;
begin
for row in select partid,ownerid,sum(stock),2 from stockperowner
where leadstateid in (2,3,4)
group by partid,ownerid
Loop
return next row;
end loop;
for row in select partid,ownerid,sum(stock),1 from stockperowner
where leadstateid in (1,3,4)
group by partid,ownerid
Loop
return next row;
end loop;
return;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE VIEW stockperowner AS
SELECT a.partid, a.ownerid, sum(a.stock) AS stock, b.leadstateid
FROM stock a
JOIN manufacturerpartpn b ON a.pnid = b.pnid
WHERE b.compatibilitygradeid <= 400
GROUP BY a.partid, a.ownerid, b.leadstateid;

CREATE OR REPLACE FUNCTION leadcompcheck_ab(assmstat boolean, leadstateid integer)
RETURNS boolean AS
$BODY$
begin
if assmstat and leadstateid in (1,3,4) then
return true;
elsif not assmstat and leadstateid in (2,3,4) then
return true;
else
return false;
end if;
end
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

CREATE TABLE pos
(
poid integer NOT NULL DEFAULT nextval(('public.pos_poid_seq'::text)::regclass),
supplierid integer,
poref citext NOT NULL,
postatusid integer,
isrfq boolean,
posupplierref citext,
issuedate date,
confirmationdate date,
confirmationref citext,
promiseddeliverydate date,
deliverydate date,
comments text,
userid integer,
currencyid integer DEFAULT 1,
exchange double precision,
printedcomment text,
ownerid integer,
suppliercontactid integer,
readydate date,
courierid integer,
couriercontact citext,
courierdate date,
shipmentdoc citext,
suppliercourier boolean,
suppliercourierdetails citext,
fob boolean,
fobmfgname integer,
attachments text,
paymentorder integer,
paymentdelivery integer,
paymentcredit integer,
creditdays integer,
currentplus boolean,
problems text,
clonedfrompoid integer,
followupcontactid integer,
lastmodifieddate timestamp without time zone,
filegenerated boolean NOT NULL DEFAULT false,
revision integer DEFAULT 0,
CONSTRAINT pos_pkey PRIMARY KEY (poid),
CONSTRAINT pos_courierid_fkey FOREIGN KEY (courierid)
REFERENCES couriers (courierid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT pos_currencyid_fkey FOREIGN KEY (currencyid)
REFERENCES currencies (currencyid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT pos_followupcontactid_fkey FOREIGN KEY (followupcontactid)
REFERENCES organizationcontacts (organizationcontactid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT pos_postatusid_fkey FOREIGN KEY (postatusid)
REFERENCES postatus (postatusid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT pos_suppliercontactid_fkey FOREIGN KEY (suppliercontactid)
REFERENCES organizationcontacts (organizationcontactid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT pos_supplierid_fkey FOREIGN KEY (supplierid)
REFERENCES organizations (organizationid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH OIDS;
ALTER TABLE pos OWNER TO postgres;

-- Index: courieridpo

-- DROP INDEX courieridpo;

CREATE INDEX courieridpo
ON pos
USING btree
(courierid);

-- Index: ix_b010e4db_b3da_4618_8328_f47d77c917a9_

-- DROP INDEX ix_b010e4db_b3da_4618_8328_f47d77c917a9_;

CREATE INDEX ix_b010e4db_b3da_4618_8328_f47d77c917a9_
ON pos
USING btree
(currencyid);

-- Index: ix_isrfqpo

-- DROP INDEX ix_isrfqpo;

CREATE INDEX ix_isrfqpo
ON pos
USING btree
(isrfq);

-- Index: ix_pospoid

-- DROP INDEX ix_pospoid;

CREATE UNIQUE INDEX ix_pospoid
ON pos
USING btree
(poid);

-- Index: owneridpo

-- DROP INDEX owneridpo;

CREATE INDEX owneridpo
ON pos
USING btree
(ownerid);

-- Index: postatusidpo

-- DROP INDEX postatusidpo;

CREATE INDEX postatusidpo
ON pos
USING btree
(postatusid);

-- Index: supplieridpo

-- DROP INDEX supplieridpo;

CREATE INDEX supplieridpo
ON pos
USING btree
(supplierid);

-- Index: useridpo

-- DROP INDEX useridpo;

CREATE INDEX useridpo
ON pos
USING btree
(userid);

CREATE TABLE poparts
(
popartid integer NOT NULL DEFAULT nextval(('public.poparts_popartid_seq'::text)::regclass),
poid integer,
partid integer,
pnid integer,
quantity integer,
supplierquantity integer,
unitprice double precision,
requestedby date,
promisedby date,
deliveredby date,
deliverywks integer,
comments citext,
currencyid integer,
statusrequest boolean,
nobid boolean,
invoiceno citext,
paymentsatus integer,
purchaseagreemet boolean,
deliveredsum integer DEFAULT 0,
fkpoitemstatusid integer,
bestprice double precision,
bestpricecomments citext,
linenumber integer,
intotal boolean NOT NULL DEFAULT true,
mpqqty integer,
lastmodifieddate timestamp without time zone,
CONSTRAINT poparts_pkey PRIMARY KEY (popartid),
CONSTRAINT poparts_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT poparts_pnid_fkey FOREIGN KEY (pnid)
REFERENCES manufacturerpartpn (pnid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT poparts_poid_fkey FOREIGN KEY (poid)
REFERENCES pos (poid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE poparts OWNER TO postgres;

-- Index: currencyidpp

-- DROP INDEX currencyidpp;

CREATE INDEX currencyidpp
ON poparts
USING btree
(currencyid);

-- Index: ix_manufacturerpartpnpoparts

-- DROP INDEX ix_manufacturerpartpnpoparts;

CREATE INDEX ix_manufacturerpartpnpoparts
ON poparts
USING btree
(pnid);

-- Index: ix_partspoparts

-- DROP INDEX ix_partspoparts;

CREATE INDEX ix_partspoparts
ON poparts
USING btree
(partid);

-- Index: ix_pospoparts

-- DROP INDEX ix_pospoparts;

CREATE INDEX ix_pospoparts
ON poparts
USING btree
(poid);

-- Index: popartid

-- DROP INDEX popartid;

CREATE INDEX popartid
ON poparts
USING btree
(popartid);

CREATE TABLE manufacturerpartpn
(
pnid integer NOT NULL DEFAULT nextval(('public.manufacturerpartpn_pnid_seq'::text)::regclass),
partid integer,
manufacturerid integer,
manufacturerpn citext,
manufacturerdatasheet text,
mpq integer,
unitid integer,
comments citext,
compatibilitygradeid integer,
pnstatusid integer,
lifecycleid integer DEFAULT 100,
translatempq boolean NOT NULL DEFAULT false,
leadstateid integer,
parentid integer,
CONSTRAINT manufacturerpartpn_pkey PRIMARY KEY (pnid),
CONSTRAINT manufacturerpartpn_compatibilitygradeid_fkey FOREIGN KEY (compatibilitygradeid)
REFERENCES partcompatibility (compatibilitygradeid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT manufacturerpartpn_lifecycleid_fkey FOREIGN KEY (lifecycleid)
REFERENCES partlifecycle (lifecycleid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT manufacturerpartpn_manufacturerid_fkey FOREIGN KEY (manufacturerid)
REFERENCES organizations (organizationid) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT manufacturerpartpn_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT manufacturerpartpn_unitid_fkey FOREIGN KEY (unitid)
REFERENCES units (unitid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE manufacturerpartpn OWNER TO postgres;

-- Index: ix_dda4c108_741e_4831_b7a9_af5ef5afbcbc_

-- DROP INDEX ix_dda4c108_741e_4831_b7a9_af5ef5afbcbc_;

CREATE INDEX ix_dda4c108_741e_4831_b7a9_af5ef5afbcbc_
ON manufacturerpartpn
USING btree
(compatibilitygradeid);

-- Index: ix_manufacturerpartpnpnid

-- DROP INDEX ix_manufacturerpartpnpnid;

CREATE UNIQUE INDEX ix_manufacturerpartpnpnid
ON manufacturerpartpn
USING btree
(pnid);

-- Index: ix_manufacturersmanufacturerpartpn

-- DROP INDEX ix_manufacturersmanufacturerpartpn;

CREATE INDEX ix_manufacturersmanufacturerpartpn
ON manufacturerpartpn
USING btree
(manufacturerid);

-- Index: ix_partlifecyclemanufacturerpartpn

-- DROP INDEX ix_partlifecyclemanufacturerpartpn;

CREATE INDEX ix_partlifecyclemanufacturerpartpn
ON manufacturerpartpn
USING btree
(lifecycleid);

-- Index: ix_partsmanufacturerpartpn

-- DROP INDEX ix_partsmanufacturerpartpn;

CREATE INDEX ix_partsmanufacturerpartpn
ON manufacturerpartpn
USING btree
(partid);

-- Index: ix_unitsmanufacturerpartpn

-- DROP INDEX ix_unitsmanufacturerpartpn;

CREATE INDEX ix_unitsmanufacturerpartpn
ON manufacturerpartpn
USING btree
(unitid);

-- Index: mpplsi

-- DROP INDEX mpplsi;

CREATE INDEX mpplsi
ON manufacturerpartpn
USING btree
(leadstateid);

CREATE TABLE allocatedassemblies
(
allocatedassembliesid integer NOT NULL DEFAULT nextval(('public.allocatedassemblies_allocatedassembliesid_seq'::text)::regclass),
assembliesbatchid integer,
partid integer,
ownerid integer,
quantity integer,
commitdate timestamp without time zone,
userid integer,
comments citext,
CONSTRAINT pk_allocatedassemblies PRIMARY KEY (allocatedassembliesid),
CONSTRAINT fk_allocatedassemblies_assembliesbatchid FOREIGN KEY (assembliesbatchid)
REFERENCES assembliesbatch (assembliesbatchid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_allocatedassemblies_partid FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE allocatedassemblies OWNER TO postgres;

-- Index: fki_allocatedassemblies_assembliesbatchid

-- DROP INDEX fki_allocatedassemblies_assembliesbatchid;

CREATE INDEX fki_allocatedassemblies_assembliesbatchid
ON allocatedassemblies
USING btree
(assembliesbatchid);

-- Index: fki_allocatedassemblies_partid

-- DROP INDEX fki_allocatedassemblies_partid;

CREATE INDEX fki_allocatedassemblies_partid
ON allocatedassemblies
USING btree
(partid);

CREATE TABLE assembliesbatch
(
assembliesbatchid integer NOT NULL DEFAULT nextval(('public.assembliesbatch_assembliesbatchid_seq'::text)::regclass),
batchid integer,
assemblyid integer,
units integer,
comments citext,
lastmodified timestamp without time zone,
ab_options citext,
buildprice double precision,
duedate timestamp with time zone DEFAULT (('now'::text)::date + '49 days'::interval),
customerid integer,
allocatedunits integer,
canbuild citext,
entrydate timestamp without time zone DEFAULT ('now'::text)::date,
assembliesbatchstatusid integer DEFAULT 1,
customername citext,
currentsort integer,
bomprice double precision,
originalunits integer,
quotationitemid integer,
CONSTRAINT assembliesbatch_pkey PRIMARY KEY (assembliesbatchid),
CONSTRAINT assembliesbatch_assembliesbatchstatus_id FOREIGN KEY (assembliesbatchstatusid)
REFERENCES assembliesbatchstatus (assembliesbatchstatusid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assembliesbatch_assemblyid_fkey FOREIGN KEY (assemblyid)
REFERENCES assemblies (assemblyid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assembliesbatch_batchid_fkey FOREIGN KEY (batchid)
REFERENCES batches (batchid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assembliesbatch_quotationitemid_fkey FOREIGN KEY (quotationitemid)
REFERENCES sales.quotationitems (quotationitemid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE assembliesbatch OWNER TO postgres;

-- Index: fki_assembliesbatch_assembliesbatchstatus_id

-- DROP INDEX fki_assembliesbatch_assembliesbatchstatus_id;

CREATE INDEX fki_assembliesbatch_assembliesbatchstatus_id
ON assembliesbatch
USING btree
(assembliesbatchstatusid);

-- Index: ix_080c8ff0_5017_42a2_a174_28095b85106e_

-- DROP INDEX ix_080c8ff0_5017_42a2_a174_28095b85106e_;

CREATE INDEX ix_080c8ff0_5017_42a2_a174_28095b85106e_
ON assembliesbatch
USING btree
(assemblyid);

CREATE TABLE assemblies
(
assemblyid integer NOT NULL DEFAULT nextval(('public.assemblies_assemblyid_seq'::text)::regclass),
assemblyname citext NOT NULL,
assemblytypeid integer DEFAULT 100,
productid integer,
leadfree boolean NOT NULL DEFAULT true,
CONSTRAINT assemblies_pkey PRIMARY KEY (assemblyid),
CONSTRAINT assemblies_assemblytypeid_fkey FOREIGN KEY (assemblytypeid)
REFERENCES assemblytype (assemblytypeid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT assemblies_productid_fkey FOREIGN KEY (productid)
REFERENCES products (productid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT uix_assemblies_assemblyname UNIQUE (assemblyname)
)
WITH OIDS;
ALTER TABLE assemblies OWNER TO postgres;

-- Index: ix_assemblytypeassemblies

-- DROP INDEX ix_assemblytypeassemblies;

CREATE INDEX ix_assemblytypeassemblies
ON assemblies
USING btree
(assemblytypeid);

-- Index: ix_leadfree

-- DROP INDEX ix_leadfree;

CREATE INDEX ix_leadfree
ON assemblies
USING btree
(leadfree);

-- Index: ix_relationship58

-- DROP INDEX ix_relationship58;

CREATE INDEX ix_relationship58
ON assemblies
USING btree
(productid);

-- Index: uix_assemblies_assemblyname

-- DROP INDEX uix_assemblies_assemblyname;

CREATE UNIQUE INDEX uix_assemblies_assemblyname
ON assemblies
USING btree
(assemblyname);

CREATE TABLE partsassembly
(
partsassemblyid integer NOT NULL DEFAULT nextval(('public.partsassembly_partsassemblyid_seq'::text)::regclass),
partid integer NOT NULL,
assemblyid integer NOT NULL,
quantity double precision,
unitid integer,
CONSTRAINT partsassembly_pkey PRIMARY KEY (partsassemblyid),
CONSTRAINT partsassembly_assemblyid_fkey FOREIGN KEY (assemblyid)
REFERENCES assemblies (assemblyid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT partsassembly_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT partsassembly_unitid_fkey FOREIGN KEY (unitid)
REFERENCES units (unitid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE partsassembly OWNER TO postgres;

-- Index: assemblyidpa

-- DROP INDEX assemblyidpa;

CREATE INDEX assemblyidpa
ON partsassembly
USING btree
(assemblyid);

-- Index: idx_u_assidpartid

-- DROP INDEX idx_u_assidpartid;

CREATE UNIQUE INDEX idx_u_assidpartid
ON partsassembly
USING btree
(partid, assemblyid);

-- Index: ix_19e2e534_c71c_4ea3_9c58_4871c4b37130_

-- DROP INDEX ix_19e2e534_c71c_4ea3_9c58_4871c4b37130_;

CREATE INDEX ix_19e2e534_c71c_4ea3_9c58_4871c4b37130_
ON partsassembly
USING btree
(partid);

-- Index: ix_5b3dd218_7383_402a_90e2_12458dd570ea_

-- DROP INDEX ix_5b3dd218_7383_402a_90e2_12458dd570ea_;

CREATE INDEX ix_5b3dd218_7383_402a_90e2_12458dd570ea_
ON partsassembly
USING btree
(assemblyid);

-- Index: ix_unitspartsassembly

-- DROP INDEX ix_unitspartsassembly;

CREATE INDEX ix_unitspartsassembly
ON partsassembly
USING btree
(unitid);

-- Index: partidpa

-- DROP INDEX partidpa;

CREATE INDEX partidpa
ON partsassembly
USING btree
(partid);

-- Index: partsassemblyid

-- DROP INDEX partsassemblyid;

CREATE INDEX partsassemblyid
ON partsassembly
USING btree
(partsassemblyid);
CREATE TABLE stocklog
(
stocklogid integer NOT NULL DEFAULT nextval(('public.stocklog_stocklogid_seq'::text)::regclass),
partid integer,
pnid integer,
ownerid integer,
quantity integer,
transtypeid integer,
out_deleted boolean,
refid integer,
poid integer,
commited boolean,
commitdate timestamp without time zone,
userid integer,
comments citext,
stocklocationid integer,
scanned boolean NOT NULL DEFAULT false,
scanneddate timestamp without time zone,
CONSTRAINT stocklog_pkey PRIMARY KEY (stocklogid),
CONSTRAINT stocklog_ownerid_fkey FOREIGN KEY (ownerid)
REFERENCES owners (ownerid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stocklog_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stocklog_pnid_fkey FOREIGN KEY (pnid)
REFERENCES manufacturerpartpn (pnid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stocklog_stocklocationid_fkey FOREIGN KEY (stocklocationid)
REFERENCES stocklocations (stocklocationid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT stocklog_transtypeid_fkey FOREIGN KEY (transtypeid)
REFERENCES transtypes (transtypeid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE stocklog OWNER TO postgres;

-- Index: ix_dfdc970a_9e18_4cf2_b28d_26c7c68d324f_

-- DROP INDEX ix_dfdc970a_9e18_4cf2_b28d_26c7c68d324f_;

CREATE INDEX ix_dfdc970a_9e18_4cf2_b28d_26c7c68d324f_
ON stocklog
USING btree
(ownerid);

-- Index: ix_manufacturerpartpnstocklog

-- DROP INDEX ix_manufacturerpartpnstocklog;

CREATE INDEX ix_manufacturerpartpnstocklog
ON stocklog
USING btree
(pnid);

-- Index: ix_partsstocklog

-- DROP INDEX ix_partsstocklog;

CREATE INDEX ix_partsstocklog
ON stocklog
USING btree
(partid);

-- Index: ix_transtypesstocklog

-- DROP INDEX ix_transtypesstocklog;

CREATE INDEX ix_transtypesstocklog
ON stocklog
USING btree
(transtypeid);

-- Index: owneridsl

-- DROP INDEX owneridsl;

CREATE INDEX owneridsl
ON stocklog
USING btree
(ownerid);

-- Index: partidsl

-- DROP INDEX partidsl;

CREATE INDEX partidsl
ON stocklog
USING btree
(partid);

-- Index: poidsl

-- DROP INDEX poidsl;

CREATE INDEX poidsl
ON stocklog
USING btree
(poid);

-- Index: referenceidsl

-- DROP INDEX referenceidsl;

CREATE INDEX referenceidsl
ON stocklog
USING btree
(refid);

-- Index: stocklogid

-- DROP INDEX stocklogid;

CREATE INDEX stocklogid
ON stocklog
USING btree
(stocklogid);

-- Index: targetidsl

-- DROP INDEX targetidsl;

CREATE INDEX targetidsl
ON stocklog
USING btree
(transtypeid);

-- Index: useridsl

-- DROP INDEX useridsl;

CREATE INDEX useridsl
ON stocklog
USING btree
(userid);

REATE TABLE stock
(
stockid integer NOT NULL DEFAULT nextval(('public.stock_stockid_seq'::text)::regclass),
partid integer,
pnid integer,
ownerid integer,
stock integer NOT NULL DEFAULT 0,
stocklocationid integer,
batchid integer,
CONSTRAINT stock_pkey PRIMARY KEY (stockid),
CONSTRAINT stock_batchid_fkey FOREIGN KEY (batchid)
REFERENCES batches (batchid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT stock_ownerid_fkey FOREIGN KEY (ownerid)
REFERENCES owners (ownerid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stock_partid_fkey FOREIGN KEY (partid)
REFERENCES parts (partid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stock_pnid_fkey FOREIGN KEY (pnid)
REFERENCES manufacturerpartpn (pnid) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT stock_stocklocationid_fkey FOREIGN KEY (stocklocationid)
REFERENCES stocklocations (stocklocationid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH OIDS;
ALTER TABLE stock OWNER TO postgres;

-- Index: ix_6f3e08e1_c643_4f37_be8a_778c97c4eccf_

-- DROP INDEX ix_6f3e08e1_c643_4f37_be8a_778c97c4eccf_;

CREATE INDEX ix_6f3e08e1_c643_4f37_be8a_778c97c4eccf_
ON stock
USING btree
(ownerid);

-- Index: ix_fa66a931_dbd2_4b59_ae2a_0fc056954753_

-- DROP INDEX ix_fa66a931_dbd2_4b59_ae2a_0fc056954753_;

CREATE INDEX ix_fa66a931_dbd2_4b59_ae2a_0fc056954753_
ON stock
USING btree
(partid);

-- Index: ix_manufacturerpartpnstock

-- DROP INDEX ix_manufacturerpartpnstock;

CREATE INDEX ix_manufacturerpartpnstock
ON stock
USING btree
(pnid);

-- Index: ownerids

-- DROP INDEX ownerids;

CREATE INDEX ownerids
ON stock
USING btree
(ownerid);

-- Index: partids

-- DROP INDEX partids;

CREATE INDEX partids
ON stock
USING btree
(partid);

2008/1/10, Sim Zacks <sim(at)compulab(dot)co(dot)il>:
> I just upgraded my database server from 8.0.1 to 8.2.4
> Most things went very well, but I have a couple of queries that really slowed down with the new server.
> On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
> (I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).
>
> The data is exactly the same on the 2 servers.
>
> To test for hardware differences, I loaded 8.0.11 onto a test server restored the database and ran the query.
> It took about 3 seconds. I then uninstalled postgresql and installed version 8.2.4 and restored the database
> and the query took about 60 seconds.
>
> On the 8.2.4 the CPU usage (as seen from top) goes up to about 97% for most of the 60 seconds of query.
> On 8.0.1, it didn't.
>
> I have the explain from both databases, if someone could help me walk through this, I would much appreciate it.

I have seen performance degradation at every new version since 7.3.
But now 8.3 is a complete disaster. It could be that my most expensive
query is just a corner case, but I don't believe it. I posted about it
but the whole thread disappeared from the archives. It can still be
found here:

http://archives.free.net.ph/message/20080105.004509.22be255d.es.html

Could you try 8.3 and see what happens? Keep the emails in case this
thread mysteriously disappears.

Regards, Clodoaldo Pinto Neto

On 1/10/08, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> I just upgraded my database server from 8.0.1 to 8.2.4
> Most things went very well, but I have a couple of queries that really slowed down with the new server.
> On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
> (I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).
>

Did you analyze the 8.2 db? AFAIK a plain vacuum doesn't gather any statistics.

Kind regards,
Isak

On Thu, Jan 10, 2008 at 01:50:42PM -0200, Clodoaldo wrote:
> I posted about it
> but the whole thread disappeared from the archives. It can still be
> found here:
>
> http://archives.free.net.ph/message/20080105.004509.22be255d.es.html

Huh? It's right there:
http://archives.postgresql.org/pgsql-general/2008-01/msg00143.php

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

2008/1/10, Martijn van Oosterhout <kleptog(at)svana(dot)org>:
> On Thu, Jan 10, 2008 at 01:50:42PM -0200, Clodoaldo wrote:
> > I posted about it
> > but the whole thread disappeared from the archives. It can still be
> > found here:
> >
> > http://archives.free.net.ph/message/20080105.004509.22be255d.es.html
>
> Huh? It's right there:
> http://archives.postgresql.org/pgsql-general/2008-01/msg00143.php

Where did you get that url? I can't find it here:

http://archives.postgresql.org/pgsql-general/2008-01/threads.php

Regards, Clodoaldo Pinto Neto

On 10/01/2008 16:11, Clodoaldo wrote:
> Where did you get that url? I can't find it here:
>
> http://archives.postgresql.org/pgsql-general/2008-01/threads.php

It's on page 2 of the list.....click "Next", and then it's a little over
half-way down.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
---------------------------------------------------------------

Clodoaldo escribió:
> 2008/1/10, Martijn van Oosterhout <kleptog(at)svana(dot)org>:

> > http://archives.postgresql.org/pgsql-general/2008-01/msg00143.php
>
> Where did you get that url? I can't find it here:
>
> http://archives.postgresql.org/pgsql-general/2008-01/threads.php

"Next page"

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com> writes:
> 2008/1/10, Martijn van Oosterhout <kleptog(at)svana(dot)org>:
>> Huh? It's right there:
>> http://archives.postgresql.org/pgsql-general/2008-01/msg00143.php

> Where did you get that url? I can't find it here:
> http://archives.postgresql.org/pgsql-general/2008-01/threads.php

Try about halfway down the "next page".

regards, tom lane

On Jan 10, 2008 9:50 AM, Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com> wrote:
> 2008/1/10, Sim Zacks <sim(at)compulab(dot)co(dot)il>:
> > I just upgraded my database server from 8.0.1 to 8.2.4
> > Most things went very well, but I have a couple of queries that really slowed down with the new server.
> > On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
> > (I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).
> >
> > The data is exactly the same on the 2 servers.
> >
> > To test for hardware differences, I loaded 8.0.11 onto a test server restored the database and ran the query.
> > It took about 3 seconds. I then uninstalled postgresql and installed version 8.2.4 and restored the database
> > and the query took about 60 seconds.
> >
> > On the 8.2.4 the CPU usage (as seen from top) goes up to about 97% for most of the 60 seconds of query.
> > On 8.0.1, it didn't.
> >
> > I have the explain from both databases, if someone could help me walk through this, I would much appreciate it.
>
> I have seen performance degradation at every new version since 7.3.

Then your experience has been exactly the opposite of mine.

> But now 8.3 is a complete disaster. It could be that my most expensive
> query is just a corner case, but I don't believe it.

So, what's the other explanation, all queries in 8.3 are slower, and
everyone who says it's faster is just lieing?

> Could you try 8.3 and see what happens? Keep the emails in case this
> thread mysteriously disappears.

Please stop the histrionics. If your new query is slower, post the
information here to help the hackers figure out why its slower and
help you fix it. There's no grand conspiracy to hide you poorly
performing query. There may be a negative reaction to your behaviour
that's hampering it getting any priority to get fixed, but I can't say
I'd blame the hackers on that one.

I meant I did Vacuum Analyze.
In any case, Aside from the vacuum analyze, I also tested it right after a database restore, so there should be no need for any maintenance features.

Sim

Isak Hansen wrote:
> On 1/10/08, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
>> I just upgraded my database server from 8.0.1 to 8.2.4
>> Most things went very well, but I have a couple of queries that really slowed down with the new server.
>> On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
>> (I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).
>>
>
>
> Did you analyze the 8.2 db? AFAIK a plain vacuum doesn't gather any statistics.
>
>
> Kind regards,
> Isak
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

Most of the queries that I have tested work on 8.2.4 at least as fast as on 8.0.1.
This one has really thrown me for a loop.

Sim

>
>> Could you try 8.3 and see what happens? Keep the emails in case this
>> thread mysteriously disappears.
>
> Please stop the histrionics. If your new query is slower, post the
> information here to help the hackers figure out why its slower and
> help you fix it. There's no grand conspiracy to hide you poorly
> performing query. There may be a negative reaction to your behaviour
> that's hampering it getting any priority to get fixed, but I can't say
> I'd blame the hackers on that one.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>

2008/1/10, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
> On Jan 10, 2008 9:50 AM, Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com> wrote:
> > 2008/1/10, Sim Zacks <sim(at)compulab(dot)co(dot)il>:
> > > I just upgraded my database server from 8.0.1 to 8.2.4
> > > Most things went very well, but I have a couple of queries that really slowed down with the new server.
> > > On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
> > > (I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).
> > >
> > > The data is exactly the same on the 2 servers.
> > >
> > > To test for hardware differences, I loaded 8.0.11 onto a test server restored the database and ran the query.
> > > It took about 3 seconds. I then uninstalled postgresql and installed version 8.2.4 and restored the database
> > > and the query took about 60 seconds.
> > >
> > > On the 8.2.4 the CPU usage (as seen from top) goes up to about 97% for most of the 60 seconds of query.
> > > On 8.0.1, it didn't.
> > >
> > > I have the explain from both databases, if someone could help me walk through this, I would much appreciate it.
> >
> > I have seen performance degradation at every new version since 7.3.
>
> Then your experience has been exactly the opposite of mine.

I suspect some developers here make a living from supporting
postgresql and have real world experience with it. I'm not sure who
they are as I don't read the list often. Are you one of them? If yes
can you tell from your clients experience that batch inserts of 800
thousands rows are faster now, especially with 8.3?

> > But now 8.3 is a complete disaster. It could be that my most expensive
> > query is just a corner case, but I don't believe it.
>
> So, what's the other explanation, all queries in 8.3 are slower, and
> everyone who says it's faster is just lieing?

Not all queries, but sure my batch insert queries are slower. I don't
know the explanation. I just don't think that batch insert queries are
irrelevant and could be treated as corner case.

> > Could you try 8.3 and see what happens? Keep the emails in case this
> > thread mysteriously disappears.
>
> Please stop the histrionics.

Yes, that was a big mistake and I apologize for it.

> If your new query is slower, post the
> information here to help the hackers figure out why its slower and
> help you fix it.

I already did it in the mentioned thread and I did that trying to help
and I don't expect any special treatment. If the developers think it
is not a priority so be it. I can just keep 8.2 until it gets
unsupported and/or I find the time and motivation to migrate to
another db server. As it is now 8.3 performance for my most important
query it totally unacceptable.

Regards, Clodoaldo Pinto Neto

On Jan 10, 2008 11:12 AM, Clodoaldo <clodoaldo(dot)pinto(dot)neto(at)gmail(dot)com> wrote:
> 2008/1/10, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
> > > I have seen performance degradation at every new version since 7.3.
> >
> > Then your experience has been exactly the opposite of mine.
>
> I suspect some developers here make a living from supporting
> postgresql and have real world experience with it. I'm not sure who
> they are as I don't read the list often. Are you one of them? If yes
> can you tell from your clients experience that batch inserts of 800
> thousands rows are faster now, especially with 8.3?

I am a user. One who is VERY happy both with the performance of
PostgreSQL and the support I get by having a direct line of support to
the developers here on these news groups. The developers make money
by working for companies that provide support. Those companies make
money by selling support. They sell support because PostgreSQL is
performant. Making it slower will not, in the long run, make them
more money.

I haven't tested 8.3 yet, as I've been too busy migrating our internal
servers from 7.4 to 8.2, and I am very very very happy with the
increase in performance we are seeing in all operations, including
bulk imports.

> > If your new query is slower, post the
> > information here to help the hackers figure out why its slower and
> > help you fix it.
>
> I already did it in the mentioned thread and I did that trying to help
> and I don't expect any special treatment. If the developers think it
> is not a priority so be it.

Well, generally performance corner cases are important. But during
the rush from late beta to release probably not as much as they would
have been before beta. They don't wanna go making large changes to
the source code to accomodate a single case if it could negatively
affect a lot of other cases.

OTOH, if your case is strong enough, then it's quite likely you could
get some work done to fix it.

> I can just keep 8.2 until it gets
> unsupported and/or I find the time and motivation to migrate to
> another db server.

Yes, because other db servers never have these types of problems...

> As it is now 8.3 performance for my most important
> query it totally unacceptable.

Please look for my post addressed to you elsewhere about this issue.

I don't an answer to your question, but an obvious difference is that
the "slow" query contains many more loops. (this may already have been
noted, I didn't see it posted however).

(showing just the loops with more than one loop)

-> Index Scan using assemblies_pkey on assemblies a (cost=0.00..0.31
rows=1 width=38) (actual time=0.007..0.009 rows=1 loops=3685)
-> Function Scan on stockperowner_lead_ab c (cost=0.00..15.00 rows=5
width=20) (actual time=0.012..3.162 rows=1694 loops=3685)
-> Index Scan using idx_u_assidpartid on partsassembly b
(cost=0.00..0.27 rows=1 width=16) (actual time=0.010..0.012 rows=1
loops=3685)
-> Hash Join (cost=97.69..531.29 rows=1250 width=21) (actual
time=2.395..78.855 rows=3851 loops=3684)
-> Seq Scan on allocatedassemblies b (cost=0.00..349.62 rows=19062
width=12) (actual time=0.009..35.493 rows=19062 loops=3684)
-> Hash Join (cost=621.63..1206.10 rows=3854 width=28) (actual
time=0.074..71.265 rows=593 loops=3684)
-> Hash Join (cost=337.11..839.61 rows=3860 width=32) (actual
time=0.057..68.467 rows=593 loops=3684)
-> Seq Scan on poparts e (cost=0.00..379.60 rows=16860 width=32)
(actual time=0.008..34.510 rows=16860 loops=3684)

On 1/10/08, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> I meant I did Vacuum Analyze.
> In any case, Aside from the vacuum analyze, I also tested it right after a database restore, so there should be no need for any maintenance features.
>

The stats didn't look too far off, no.

Perhaps a suboptimal plan is picked due to configuration issues, e.g.
memory constraints? Could you post your postgresql.conf as well?

Kind regards,
Isak

> Sim
>
> Isak Hansen wrote:
> > On 1/10/08, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> >> I just upgraded my database server from 8.0.1 to 8.2.4
> >> Most things went very well, but I have a couple of queries that really slowed down with the new server.
> >> On 8.0.1 the query took less then 3 seconds to complete. On 8.2.4 the same query
> >> (I vacuumed the database before running the query) takes 60 seconds (10 minutes with Explain Analyze).
> >>
> >
> >
> > Did you analyze the 8.2 db? AFAIK a plain vacuum doesn't gather any statistics.
> >
> >
> > Kind regards,
> > Isak
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> > choose an index scan if your joining column's datatypes do not
> > match
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

It does contain a lot more loops, but it is the exact same query, so I don't understand why it would use that kind of plan

sim

Bricklen Anderson wrote:
> I don't an answer to your question, but an obvious difference is that
> the "slow" query contains many more loops. (this may already have been
> noted, I didn't see it posted however).
>
> (showing just the loops with more than one loop)
>
> -> Index Scan using assemblies_pkey on assemblies a (cost=0.00..0.31
> rows=1 width=38) (actual time=0.007..0.009 rows=1 loops=3685)
> -> Function Scan on stockperowner_lead_ab c (cost=0.00..15.00 rows=5
> width=20) (actual time=0.012..3.162 rows=1694 loops=3685)
> -> Index Scan using idx_u_assidpartid on partsassembly b
> (cost=0.00..0.27 rows=1 width=16) (actual time=0.010..0.012 rows=1
> loops=3685)
> -> Hash Join (cost=97.69..531.29 rows=1250 width=21) (actual
> time=2.395..78.855 rows=3851 loops=3684)
> -> Seq Scan on allocatedassemblies b (cost=0.00..349.62 rows=19062
> width=12) (actual time=0.009..35.493 rows=19062 loops=3684)
> -> Hash Join (cost=621.63..1206.10 rows=3854 width=28) (actual
> time=0.074..71.265 rows=593 loops=3684)
> -> Hash Join (cost=337.11..839.61 rows=3860 width=32) (actual
> time=0.057..68.467 rows=593 loops=3684)
> -> Seq Scan on poparts e (cost=0.00..379.60 rows=16860 width=32)
> (actual time=0.008..34.510 rows=16860 loops=3684)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

> Perhaps a suboptimal plan is picked due to configuration issues, e.g.
> memory constraints? Could you post your postgresql.conf as well?

Below is the postgresql.conf file for 8.2.4. The server has 2 GB of RAM and it was not maxed out when I ran the query.
As I mentioned, I tried running both 8.0.11 and 8.2.4 on the same hardware, so that I would see if it was a difference in the hardware or the database.

Sim

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Please note that re-commenting a setting is NOT sufficient to revert it
# to the default value, unless you restart the server.
#
# Any option can also be given as a command line switch to the server,
# e.g., 'postgres -c log_connections=on'. Some options can be changed at
# run-time with the 'SET' SQL command.
#
# This file is read on server startup and when the server receives a
# SIGHUP. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# settings, which are marked below, require a server shutdown and restart
# to take effect.
#
# Memory units: kB = kilobytes MB = megabytes GB = gigabytes
# Time units: ms = milliseconds s = seconds min = minutes h = hours d = days

#---------------------------------------------------------------------------
# FILE LOCATIONS
#---------------------------------------------------------------------------

# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'# use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'# host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf'# ident configuration file
# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)'# write an extra PID file
# (change requires restart)

#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'# what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
#port = 5432# (change requires restart)
max_connections = 100# (change requires restart)
# Note: increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction). You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 3# (change requires restart)
#unix_socket_directory = ''# (change requires restart)
#unix_socket_group = ''# (change requires restart)
#unix_socket_permissions = 0777# octal
# (change requires restart)
#bonjour_name = ''# defaults to the computer name
# (change requires restart)

# - Security & Authentication -

#authentication_timeout = 1min# 1s-600s
#ssl = off# (change requires restart)
#password_encryption = on
#db_user_namespace = off

# Kerberos
#krb_server_keyfile = ''# (change requires restart)
#krb_srvname = 'postgres'# (change requires restart)
#krb_server_hostname = ''# empty string matches any keytab entry
# (change requires restart)
#krb_caseins_users = off# (change requires restart)

# - TCP Keepalives -
# see 'man 7 tcp' for details

#tcp_keepalives_idle = 0# TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0# TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0# TCP_KEEPCNT;
# 0 selects the system default

#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 24MB# min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB# min 800kB
#max_prepared_transactions = 5# can be 0 or more
# (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1MB# min 64kB
#maintenance_work_mem = 16MB# min 1MB
#max_stack_depth = 2MB# min 100kB

# - Free Space Map -

max_fsm_pages = 153600# min max_fsm_relations*16, 6 bytes each
# (change requires restart)
#max_fsm_relations = 1000# min 100, ~70 bytes each
# (change requires restart)

# - Kernel Resource Usage -

#max_files_per_process = 1000# min 25
# (change requires restart)
#shared_preload_libraries = ''# (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0# 0-1000 milliseconds
#vacuum_cost_page_hit = 1# 0-10000 credits
#vacuum_cost_page_miss = 10# 0-10000 credits
#vacuum_cost_page_dirty = 20# 0-10000 credits
#vacuum_cost_limit = 200# 0-10000 credits

# - Background writer -

#bgwriter_delay = 200ms# 10-10000ms between rounds
#bgwriter_lru_percent = 1.0# 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5# 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333# 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5# 0-1000 buffers max written/round

#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

#fsync = on# turns forced synchronization on or off
#wal_sync_method = fsync# the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on# recover from partial page writes
#wal_buffers = 64kB# min 32kB
# (change requires restart)
#commit_delay = 0# range 0-100000, in microseconds
#commit_siblings = 5# range 1-1000

# - Checkpoints -

#checkpoint_segments = 3# in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min# range 30s-1h
#checkpoint_warning = 30s# 0 is off

# - Archiving -

#archive_command = ''# command to use to archive a logfile segment
#archive_timeout = 0# force a logfile segment switch after this
# many seconds; 0 is off

#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0# measured on an arbitrary scale
#random_page_cost = 4.0# same scale as above
#cpu_tuple_cost = 0.01# same scale as above
#cpu_index_tuple_cost = 0.005# same scale as above
#cpu_operator_cost = 0.0025# same scale as above
#effective_cache_size = 128MB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5# range 1-10
#geqo_pool_size = 0# selects default based on effort
#geqo_generations = 0# selects default based on effort
#geqo_selection_bias = 2.0# range 1.5-2.0

# - Other Planner Options -

#default_statistics_target = 10# range 1-1000
#constraint_exclusion = off
#from_collapse_limit = 8
#join_collapse_limit = 8# 1 disables collapsing of explicit
# JOINs

#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'# Valid values are combinations of
# stderr, syslog and eventlog,
# depending on platform.

# This is used when logging to stderr:
#redirect_stderr = off# Enable capturing of stderr into log
# files
# (change requires restart)

# These are only used if redirect_stderr is on:
#log_directory = 'pg_log'# Directory where log files are written
# Can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.
# Can include strftime() escapes
#log_truncate_on_rotation = off # If on, any existing log file of the same
# name as the new log file will be
# truncated rather than appended to. But
# such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
#log_rotation_age = 1d# Automatic rotation of logfiles will
# happen after that time. 0 to
# disable.
#log_rotation_size = 10MB# Automatic rotation of logfiles will
# happen after that much log
# output. 0 to disable.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# - When to Log -

#client_min_messages = notice# Values, in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error

#log_min_messages = notice# Values, in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic

#log_error_verbosity = default# terse, default, or verbose messages

#log_min_error_statement = error# Values in order of increasing severity:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# fatal
# panic (effectively off)

#log_min_duration_statement = -1# -1 is disabled, 0 logs all statements
# and their durations.

#silent_mode = off# DO NOT USE without syslog or
# redirect_stderr
# (change requires restart)

# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_line_prefix = ''# Special values:
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = PID
# %t = timestamp (no milliseconds)
# %m = timestamp with milliseconds
# %i = command tag
# %c = session id
# %l = session line number
# %s = session start timestamp
# %x = transaction id
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_statement = 'none'# none, ddl, mod, all
#log_hostname = off

#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------

# - Query/Index Statistics Collector -

#stats_command_string = on
#update_process_title = on

#stats_start_collector = on# needed for block or row stats
# (change requires restart)
#stats_block_level = off
stats_row_level = on
#stats_reset_on_server_start = off# (change requires restart)

# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off

#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------

autovacuum = on# enable autovacuum subprocess?
# 'on' requires stats_start_collector
# and stats_row_level to also be on
#autovacuum_naptime = 1min# time between autovacuum runs
autovacuum_vacuum_threshold = 250# min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 125# min # of tuple updates before
# analyze
autovacuum_vacuum_scale_factor = 0.1# fraction of rel size before
# vacuum
autovacuum_analyze_scale_factor = 0.05# fraction of rel size before
# analyze
#autovacuum_freeze_max_age = 200000000# maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = -1# default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit

#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses
# the default
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#statement_timeout = 0 # 0 is disabled
#vacuum_freeze_min_age = 100000000

# - Locale and Formatting -

datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ
# environment setting
#timezone_abbreviations = 'Default' # select the set of available timezone
# abbreviations. Currently, there are
# Default
# Australia
# India
# However you can also create your own
# file in share/timezonesets/.
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database
# encoding

# These settings are initialized by initdb -- they might be changed
lc_messages = 'C' # locale for system error message
# strings
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting

# - Other Defaults -

#explain_pretty_print = on
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''

#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------

#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.

#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------

# - Previous Postgres Versions -

add_missing_from = on
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#standard_conforming_strings = off
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = on

# - Other Platforms & Clients -

#transform_null_equals = off

#---------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#---------------------------------------------------------------------------

#custom_variable_classes = '' # list of custom variable class names

On Jan 10, 2008 10:50 AM, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> I meant I did Vacuum Analyze.
> In any case, Aside from the vacuum analyze, I also tested it right after a database restore, so there should be no need for any maintenance features.

FYI, a restore does NOT restore the stats, nor does it automatically
update them. You have to run an analyze after a restore to get the
stats updated.

On Jan 10, 2008 12:33 PM, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> > Perhaps a suboptimal plan is picked due to configuration issues, e.g.
> > memory constraints? Could you post your postgresql.conf as well?
>
> Below is the postgresql.conf file for 8.2.4. The server has 2 GB of RAM and it was not maxed out when I ran the query.
> As I mentioned, I tried running both 8.0.11 and 8.2.4 on the same hardware, so that I would see if it was a difference in the hardware or the database.
>
> # -----------------------------
> # PostgreSQL configuration file
> # -----------------------------

> shared_buffers = 24MB# min 128kB or max_connections*16kB
That's really low. Try setting it to something a bit more aggressive,
say 100MB to 500MB. On a machine with 2 Gig ram, that's a pretty
reasonable range.

> #work_mem = 1MB# min 64kB
Try setting this a little higher, say 16 to 32 Megs.

On 1/10/08, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Jan 10, 2008 12:33 PM, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> > > Perhaps a suboptimal plan is picked due to configuration issues, e.g.
> > > memory constraints? Could you post your postgresql.conf as well?
> >
> > Below is the postgresql.conf file for 8.2.4. The server has 2 GB of RAM and it was not maxed out when I ran the query.
> > As I mentioned, I tried running both 8.0.11 and 8.2.4 on the same hardware, so that I would see if it was a difference in the hardware or the database.
> >
> > # -----------------------------
> > # PostgreSQL configuration file
> > # -----------------------------
>
> > shared_buffers = 24MB# min 128kB or max_connections*16kB
> That's really low. Try setting it to something a bit more aggressive,
> say 100MB to 500MB. On a machine with 2 Gig ram, that's a pretty
> reasonable range.
>
> > #work_mem = 1MB# min 64kB
> Try setting this a little higher, say 16 to 32 Megs.
>

Also, effective_cache_size should be about 1GB on a server with 2GB ram.

See <http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm>
for a quick, up-to-date, intro to postgres tuning.

Kind regards,
Isak

Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
> I just upgraded my database server from 8.0.1 to 8.2.4
> Most things went very well, but I have a couple of queries that really slowed down with the new server.

The core of the problem seems to be the rowcount misestimation here:

> -> Merge Left Join (cost=5816.85..5948.10 rows=1245 width=82) (actual time=1169.837..1220.895 rows=3685 loops=1)
> Merge Cond: (("outer".batchid = "inner".refid) AND ("outer".partid = "inner".partid))
> Filter: (COALESCE("inner".commited, false) = false)

vs in 8.2

> -> Merge Left Join (cost=3984.15..4111.60 rows=1 width=28) (actual time=319.642..348.285 rows=3685 loops=1)
> Merge Cond: ((d.batchid = f.refid) AND (e.partid = f.partid))
> Filter: (NOT COALESCE(f.commited, false))

The single-row estimate causes it to go for nestloops at all the
higher join levels, and when the actual result size is 3685 rows, of
course it takes 3685 times longer than the planner expected :-(

I assume that the original query is something along the lines of

d left join f on (...) where coalesce(f.commited, false) = false

I traced through what would happen here, and found that:

* 8.2 changes the "boolvar = false" clause to "NOT boolvar", because it
wants to be able to recognize these equivalent forms as equivalent.
8.0 just leaves it as-is.

* 8.0 can't figure out anything about a COALESCE, so it uses the
fallback DEFAULT_EQ_SEL (0.005) selectivity estimate for what it sees
as an equality clause. This is apparently close enough to be within a
factor of 3 of reality.

* 8.2 sees a NOT clause, which clause_selectivity() figures has a
selectivity of 1.0 minus the selectivity of the argument, which is
a COALESCE clause, which the recursive call to clause_selectivity()
doesn't know anything about and so punts ... returning 1.0 which is
its default for unknown clause types. So we end up with a selectivity
estimate of exactly 0.0, pinning the estimated join size to the minimum
of 1 row.

The default 1.0 selectivity estimate seems fairly silly; in other cases
where the planner really has no idea about the expected value of a
boolean expression, we use 0.5. On studying the CVS history, it looks
like I inserted that in revision 1.24 of clausesel.c, and I think that I
must have misinterpreted what was happening in the previous state of the
code:

static Cost
compute_selec(Query *root, List *clauses, List *or_selectivities)
{
Cost s1 = 0;
List *clause = lfirst(clauses);

if (clause == NULL)
s1 = 1.0;
else if (IsA(clause, Param))
{
/* XXX How're we handling this before?? -ay */
s1 = 1.0;
}
else if (IsA(clause, Const))
s1 = ((bool) ((Const *) clause)->constvalue) ? 1.0 : 0.0;
else if (IsA(clause, Var))
...

The reason for the first bit is that a NIL list of clauses means no
WHERE clause, so 1.0 selectivity is actually right in that case.
The 1.0 default for a Param is pretty silly (possibly AY made the same
mistake as me at some earlier point). If you look further down in the
routine you see 0.5 being used in default cases, but if you don't study
the whole thing you might well think it was intending 1.0 as default.

So I think we should change the default to 0.5 for 8.3, but seeing that
it's been this way for 8 years (!) I'm a bit hesitant to back-patch.
Comments anyone?

In the meantime, Sim would probably have better luck if he restructured
this particular clause in some other way, say

where f.commited is not true
or
where f.commited = false or f.commited is null

Note also that he really ought to move up to 8.2.6, as 8.2.4 is not
very sane about what IS NULL means for a left join's result.

regards, tom lane

I changed it to "where f.commited is not true" and the query now takes 1 second as opposed to 60.
(much faster then the 3 seconds it took on 8.0.1, which could also be because of the coalesce there)
Is it considered better practice (or more efficient) to always use (x is not or x=value)
instead of coalesce? Or does it make more sense to turn on the option "transform_null_equals"?

Thank you much
Sim

> I assume that the original query is something along the lines of
>
> d left join f on (...) where coalesce(f.commited, false) = false
>
>
> In the meantime, Sim would probably have better luck if he restructured
> this particular clause in some other way, say
>
> where f.commited is not true
> or
> where f.commited = false or f.commited is null
>
> Note also that he really ought to move up to 8.2.6, as 8.2.4 is not
> very sane about what IS NULL means for a left join's result.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Thanks for the tuning tips. I'll definitely be taking advantage of them.
Problem solved, Tom Lane found that using coalesce in my query the way I
did caused it to make a bad estimation, when I changed it the query
went from 60 seconds to 1 second.

Sim

> See <http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm>
> for a quick, up-to-date, intro to postgres tuning.
>
>
> Kind regards,
> Isak
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

Hello

On 11/01/2008, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> I changed it to "where f.commited is not true" and the query now takes 1 second as opposed to 60.
> (much faster then the 3 seconds it took on 8.0.1, which could also be because of the coalesce there)
> Is it considered better practice (or more efficient) to always use (x is not or x=value)
> instead of coalesce? Or does it make more sense to turn on the option "transform_null_equals"?
>

You can use without coalesce() = some operator IS DISTINCT FROM ... .
Use coalesce only if you need some NON NULL value.

for you sample

where f.commited IS DISTINCT FROM true;

operator IS DISTINCT FROM is NULL insensitive

Regards
Pavel Stehule

> Thank you much
> Sim
>
> > I assume that the original query is something along the lines of
> >
> > d left join f on (...) where coalesce(f.commited, false) = false
> >
> >
> > In the meantime, Sim would probably have better luck if he restructured
> > this particular clause in some other way, say
> >
> > where f.commited is not true
> > or
> > where f.commited = false or f.commited is null
> >
> > Note also that he really ought to move up to 8.2.6, as 8.2.4 is not
> > very sane about what IS NULL means for a left join's result.
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

How would you rewrite something like:
WHERE (COALESCE(b.quantity, 0) - COALESCE(b.deliveredsum, 0)) > 0;
I could write:
where case when b.quantity is null then 0 else b.quantity end - case when b.deliveredsum is null then 0 else b.deliveredsum end > 0

It is butt ugly, but is that the most efficient way to write it in 8.2.4?

Sim

Pavel Stehule wrote:
> Hello
>
> On 11/01/2008, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
>> I changed it to "where f.commited is not true" and the query now takes 1 second as opposed to 60.
>> (much faster then the 3 seconds it took on 8.0.1, which could also be because of the coalesce there)
>> Is it considered better practice (or more efficient) to always use (x is not or x=value)
>> instead of coalesce? Or does it make more sense to turn on the option "transform_null_equals"?
>>
>
> You can use without coalesce() = some operator IS DISTINCT FROM ... .
> Use coalesce only if you need some NON NULL value.
>
> for you sample
>
> where f.commited IS DISTINCT FROM true;
>
> operator IS DISTINCT FROM is NULL insensitive
>
> Regards
> Pavel Stehule
>
>> Thank you much
>> Sim
>>
>>> I assume that the original query is something along the lines of
>>>
>>> d left join f on (...) where coalesce(f.commited, false) = false
>>>
>>>
>>> In the meantime, Sim would probably have better luck if he restructured
>>> this particular clause in some other way, say
>>>
>>> where f.commited is not true
>>> or
>>> where f.commited = false or f.commited is null
>>>
>>> Note also that he really ought to move up to 8.2.6, as 8.2.4 is not
>>> very sane about what IS NULL means for a left join's result.
>>>
>>> regards, tom lane
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 6: explain analyze is your friend
>>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

Actually I just checked and the plan is exactly the same for those 2 clauses.

-------- Original Message --------
Subject: Re:8.2.4 serious slowdown
From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
To:
Date: Sunday, January 13, 2008 07:59:22 AM

> How would you rewrite something like:
> WHERE (COALESCE(b.quantity, 0) - COALESCE(b.deliveredsum, 0)) > 0;
> I could write:
> where case when b.quantity is null then 0 else b.quantity end - case
> when b.deliveredsum is null then 0 else b.deliveredsum end > 0
>
> It is butt ugly, but is that the most efficient way to write it in 8.2.4?
>
> Sim
>

2008/1/13, Sim Zacks <sim(at)compulab(dot)co(dot)il>:
> How would you rewrite something like:
> WHERE (COALESCE(b.quantity, 0) - COALESCE(b.deliveredsum, 0)) > 0;
> I could write:
> where case when b.quantity is null then 0 else b.quantity end - case when b.deliveredsum is null then 0 else b.deliveredsum end > 0
>
> It is butt ugly, but is that the most efficient way to write it in 8.2.4?

I don't know if the plan would be the same but this is a bit clearer:

WHERE COALESCE(b.quantity, 0) > COALESCE(b.deliveredsum, 0)

Regards, Clodoaldo Pinto Neto

> I don't know if the plan would be the same but this is a bit clearer:
>
> WHERE COALESCE(b.quantity, 0) > COALESCE(b.deliveredsum, 0)

That should be true, but sometimes we get deliveries of greater quantity then we ordered.
I just want to know the times when I haven't gotten the complete order yet.
If we get more then we ordered, I don't want it to be in this query.

(attribution restored)
Clodoaldo wrote:
> > I don't know if the plan would be the same but this is a bit clearer:
> >
> > WHERE COALESCE(b.quantity, 0) > COALESCE(b.deliveredsum, 0)

Sim Zacks wrote:
> That should be true, but sometimes we get deliveries of greater quantity
> then we ordered.
> I just want to know the times when I haven't gotten the complete order yet.
> If we get more then we ordered, I don't want it to be in this query.

Huh?

How does that relate to the suggestion?

The suggested expression is mathematically equivalent to and perfectly
substitutable for the original.

--
Lew

Apparently I was suffering from brain freeze.
sim

Lew wrote:
> (attribution restored)
> Clodoaldo wrote:
>> > I don't know if the plan would be the same but this is a bit clearer:
>> >
>> > WHERE COALESCE(b.quantity, 0) > COALESCE(b.deliveredsum, 0)
>
> Sim Zacks wrote:
>> That should be true, but sometimes we get deliveries of greater
>> quantity then we ordered.
>> I just want to know the times when I haven't gotten the complete order
>> yet.
>> If we get more then we ordered, I don't want it to be in this query.
>
> Huh?
>
> How does that relate to the suggestion?
>
> The suggested expression is mathematically equivalent to and perfectly
> substitutable for the original.
>

His agony? Do they not know how to paint a resolute death? Yes, for
the same Saint Luke paints the death of Saint Stephen as braver than that of
Jesus Christ.

They make Him, therefore, capable of fear, before the necessity of dying has
come, and then altogether brave.

But when they make Him so troubled, it is when He afflicts Himself; and when
men afflict Him, He is altogether strong.

801. Proof of Jesus Christ.--The supposition that the apostles were
impostors is very absurd. Let us think it out. Let us imagine those twelve
men, assembled after the death of Jesus Christ, plotting to say that He was
risen. By this they attack all the powers. The heart of man is strangely
inclined to fickleness, to change, to promises, to gain. However little any
of them might have been led astray by all these attractions, nay more, by
the fear of prisons, tortures, and death, they were lost. Let us follow up
this thought.

802. The apostles were either deceived or deceivers. Either supposition has
difficulties; for it is not possible to mistake a man raised from the
dead...

While Jesus Christ was with them, He could sustain them. But, after that, if
He did not appear to them, who in

who before had serious thoughts,
had their awakenings and convictions greatly increased. There were many
instances of persons who came from abroad on visits, or on business, who
had not been long here, before, to all appearances, they were savingly
wrought upon, and partook of that shower of divine blessing which God
rained down here, and went home rejoicing; till at length the same work
began evidently to appear and prevail in several other towns in the
county.

In the month of March, the people in South-Hadley begun to be seized
with deep concern about the things of religion; which very soon became
universal. The work of God has been very wonderful there; not much, if
any thing, short of what it has been here, in proportion to the size of
the place. About the same time, it began to break forth in the west part
of Suffield (where it also has been very great), and soon spread into
all parts of the town. It appeared at Sunderland, and soon overspread
the town: and I believe was, for a season, not less remarkable than it
was here. About the same time it began to appear in a part of Deerfield,
called Green River, and afterwards filled the town, and there has been a
glorious work there. It began also to be manifest, in the south part of
Hatfield, in a place call the Hill, and the whole town, in the second
week in April, seemed to be seized, as it were at once, with concern
about the things of religion; and the work of God has been great there.
There has been also a very general awakening at West-Springfield, and
Long Meadow; and in Enfield there was for a time a pretty general
concern amongst some who before had been very loose persons. About the
same time that this appeared at Enfield, the Rev. Mr. Bull, of
Westfield, informed me, that there had been a great alteration there,
and that more had been done in one week, than in seven years before.
Something of this work likewise appeared in the

point beyond which our senses
can no longer perceive anything, although by its nature it is infinitely
divisible.

Of these two Infinites of science, that of greatness is the most palpable,
and hence a few persons have pretended to know all things. "I will speak of
the whole," said Democritus.

But the infinitely little is the least obvious. Philosophers have much
oftener claimed to have reached it, and it is here they have all stumbled.
This has given rise to such common titles as First Principles, Principles of
Philosophy, and the like, as ostentatious in fact, though not in appearance,
as that one which blinds us, De omni scibili.5

We naturally believe ourselves far more capable of reaching the centre of
things than of embracing their circumference. The visible extent of the
world visibly exceeds us; but as we exceed little things, we think ourselves
more capable of knowing them. And yet we need no less capacity for attaining
the Nothing than the All. Infinite capacity is required for both, and it
seems to me that whoever shall have understood the ultimate principles of
being might also attain to the knowledge of the Infinite. The one depends on
the other, and one leads to the other. These extremes meet and reunite by
force of distance and find each other in God, and in God alone.

Let us, then, take our compass; we are something, and we are not everything.
The nature of our existence hides from us the knowledge of first beginnings
which are born of the Nothing; and the littleness of our being conceals from
us the sight of the Infinite.

Our intellect holds the same position in the world of thought as our body
occupies in the exp

gave orders that the burning fiery furnace should be heated
seven times hotter than it was before; doubtless, it was raised to the
utmost degree of fierceness that human art could raise it. But the great
God is also willing to show his wrath, and magnify his awful majesty and
mighty power in the extreme sufferings of his enemies. Rom. 9:22. "What
if God, willing to show his wrath, and to make his power known, endured
with much long-suffering the vessels of wrath fitted to destruction?"
And seeing this is his design, and what he has determined, even to show
how terrible the unrestrained wrath, the fury and fierceness of Jehovah
is, he will do it to effect. There will be something accomplished and
brought to pass that will be dreadful with a witness. When the great and
angry God hath risen up and executed his awful vengeance on the poor
sinner, and the wretch is actually suffering the infinite weight and
power of his indignation, then will God call upon the whole universe to
behold that awful majesty and mighty power that is to be seen in it.
Isa. 33:12-14. "And the people shall be as the burnings of lime, as
thorns cut up shall they be burnt in the fire. Hear ye that are far off,
what I have done; and ye that are near, acknowledge my might. The
sinners in Zion are afraid; fearful