IGNORE Re: explain problem

I found a slight problem with my test case. I will post again when it is correct.

Sim Zacks wrote:
> I have 2 databases that are supposed to be identical.
> In one a specific query goes very fast and the other one the same query
> goes slow.
>
> I checked the data and it is identical.
> I checked the indices and constraints and they are identical.
>
> I vacuumed both databases immediately before I ran the Explain Analyze
> and they are also different.
> Can someone tell me what I am missing?
>
> Query:
> select *
> FROM stat_allocated_components a
> JOIN ( SELECT stat_allocated_components.partid,
> stat_allocated_components.leadfree,
> max(stat_allocated_components.duedate) AS duedate
> FROM stat_allocated_components
> WHERE stat_allocated_components.quantity < 0
> GROUP BY stat_allocated_components.partid,
> stat_allocated_components.leadfree) b ON
> b.partid = a.partid AND b.duedate = a.duedate AND a.leadfree = b.leadfree
> JOIN ( SELECT stat_allocated_components.partid,
> stat_allocated_components.duedate,
> stat_allocated_components.leadfree,
> max(stat_allocated_components.assembliesbatchid) AS
> assembliesbatchid
> FROM stat_allocated_components
> WHERE stat_allocated_components.quantity < 0
> GROUP BY stat_allocated_components.partid,
> stat_allocated_components.duedate,
> stat_allocated_components.leadfree) c ON a.partid = c.partid AND
> a.assembliesbatchid =
> c.assembliesbatchid AND c.duedate = b.duedate AND c.leadfree = a.leadfree
> JOIN ( SELECT a.partid, b.leadfree, sum(
> CASE
> WHEN a.quantity > 0 THEN a.quantity
> ELSE 0::bigint
> END) AS ontheway, min(a.postatusid) AS minpostatusid
> FROM stat_allocated_components a
> JOIN leadstatebools b ON a.leadstateid = b.leadstateid
> GROUP BY a.partid, b.leadfree) d ON a.partid = d.partid AND a.leadfree
> = d.leadfree;
>
> Table Structures:
> -- Table: stat_allocated_components
>
> -- DROP TABLE stat_allocated_components;
>
> CREATE TABLE stat_allocated_components
> (
> id serial NOT NULL,
> partid integer,
> quantity bigint,
> assembliesbatchid integer,
> assemblyname citext,
> duedate timestamp with time zone,
> leadfree boolean,
> popartid integer,
> l bigint DEFAULT 0,
> lf bigint DEFAULT 0,
> lfb bigint DEFAULT 0,
> lbp bigint DEFAULT 0,
> previouscommitmentlf bigint DEFAULT 0,
> previouscommitmentl bigint DEFAULT 0,
> previouspol bigint DEFAULT 0,
> previouspolf bigint DEFAULT 0,
> previouspolfb bigint DEFAULT 0,
> stock bigint DEFAULT 0,
> instock boolean NOT NULL DEFAULT false,
> leadstateid integer,
> postatusid integer,
> previouslfbforlf bigint,
> previouslfbforl bigint,
> balance bigint,
> CONSTRAINT stat_allocated_components_pkey PRIMARY KEY (id)
> )
> WITH OIDS;
> ALTER TABLE stat_allocated_components OWNER TO postgres;
>
>
> -- Index: idx_statassembliesbatchid
>
> -- DROP INDEX idx_statassembliesbatchid;
>
> CREATE INDEX idx_statassembliesbatchid
> ON stat_allocated_components
> USING btree
> (assembliesbatchid);
>
> -- Index: idx_statduedate
>
> -- DROP INDEX idx_statduedate;
>
> CREATE INDEX idx_statduedate
> ON stat_allocated_components
> USING btree
> (duedate);
>
> -- Index: idx_statleadfree
>
> -- DROP INDEX idx_statleadfree;
>
> CREATE INDEX idx_statleadfree
> ON stat_allocated_components
> USING btree
> (leadfree);
>
> -- Index: idx_statpartid
>
> -- DROP INDEX idx_statpartid;
>
> CREATE INDEX idx_statpartid
> ON stat_allocated_components
> USING btree
> (partid);
>
>
>
> -- Table: leadstatebools
>
> -- DROP TABLE leadstatebools;
>
> CREATE TABLE leadstatebools
> (
> leadstateboolid serial NOT NULL,
> leadstateid integer,
> leadfree boolean,
> CONSTRAINT leadstatebools_pkey PRIMARY KEY (leadstateboolid),
> CONSTRAINT leadstatebools_leadstateid_fkey FOREIGN KEY (leadstateid)
> REFERENCES leadstates (leadstateid) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH OIDS;
> ALTER TABLE leadstatebools OWNER TO postgres;
>
>
> -- Index: lsblf
>
> -- DROP INDEX lsblf;
>
> CREATE INDEX lsblf
> ON leadstatebools
> USING btree
> (leadfree);
>
> -- Index: lsblsi
>
> -- DROP INDEX lsblsi;
>
> CREATE INDEX lsblsi
> ON leadstatebools
> USING btree
> (leadstateid);
>
>
> Fast Explain:
> Nested Loop (cost=363.39..383.04 rows=1 width=246) (actual
> time=22.365..22.365 rows=0 loops=1)
> Join Filter: (("inner".assembliesbatchid = "outer".assembliesbatchid)
> AND ("outer".leadfree =
> "inner".leadfree) AND ("outer".duedate = "inner".duedate))
> -> Hash Join (cost=363.39..369.78 rows=1 width=71) (actual
> time=22.357..22.357 rows=0 loops=1)
> Hash Cond: (("outer".duedate = "inner".duedate) AND
> ("outer".leadfree = "inner".leadfree)
> AND ("outer".partid = "inner".partid))
> -> Subquery Scan b (cost=116.19..119.09 rows=232 width=13)
> (never executed)
> -> HashAggregate (cost=116.19..116.77 rows=232 width=13)
> (never executed)
> -> Seq Scan on stat_allocated_components
> (cost=0.00..98.84 rows=2314 width=13)
> (never executed)
> Filter: (quantity < 0)
> -> Hash (cost=247.16..247.16 rows=5 width=58) (actual
> time=22.330..22.330 rows=0 loops=1)
> -> Hash Join (cost=241.89..247.16 rows=5 width=58)
> (actual time=22.322..22.322
> rows=0 loops=1)
> Hash Cond: (("outer".leadfree = "inner".leadfree)
> AND ("outer".partid =
> "inner".partid))
> -> Subquery Scan c (cost=121.98..124.88 rows=232
> width=17) (never executed)
> -> HashAggregate (cost=121.98..122.56
> rows=232 width=17) (never executed)
> -> Seq Scan on
> stat_allocated_components (cost=0.00..98.84
> rows=2314 width=17) (never executed)
> Filter: (quantity < 0)
> -> Hash (cost=119.88..119.88 rows=7 width=41)
> (actual time=22.295..22.295
> rows=0 loops=1)
> -> Subquery Scan d (cost=119.75..119.88
> rows=7 width=41) (actual
> time=22.288..22.288 rows=0 loops=1)
> -> HashAggregate (cost=119.75..119.81
> rows=7 width=17) (actual
> time=22.279..22.279 rows=0 loops=1)
> -> Hash Join (cost=1.07..119.68
> rows=7 width=17) (actual
> time=22.270..22.270 rows=0 loops=1)
> Hash Cond:
> ("outer".leadstateid = "inner".leadstateid)
> -> Seq Scan on
> stat_allocated_components a
> (cost=0.00..92.27 rows=2627 width=20) (actual time=0.029..12.016
> rows=2627 loops=1)
> -> Hash (cost=1.06..1.06
> rows=6 width=5) (actual
> time=0.125..0.125 rows=0 loops=1)
> -> Seq Scan on
> leadstatebools b (cost=0.00..1.06
> rows=6 width=5) (actual time=0.065..0.089 rows=6 loops=1)
> -> Index Scan using idx_statpartid on stat_allocated_components a
> (cost=0.00..13.16 rows=5
> width=175) (never executed)
> Index Cond: (a.partid = "outer".partid)
> Total runtime: 22.677 ms
>
>
> Slow Explain:
> Nested Loop (cost=3098.46..3515.36 rows=14 width=246) (actual
> time=177.039..11912.692 rows=594 loops=1)
> Join Filter: (("inner".leadfree = "outer".leadfree) AND
> ("inner".partid = "outer".partid))
> -> Nested Loop (cost=1929.33..2169.49 rows=1 width=205) (actual
> time=109.737..3417.617 rows=594 loops=1)
> Join Filter: (("outer".duedate = "inner".duedate) AND
> ("inner".leadfree = "outer".leadfree) AND ("inner".assembliesbatchid =
> "outer".assembliesbatchid))
> -> Merge Join (cost=1929.33..1951.49 rows=16 width=30) (actual
> time=109.472..141.396 rows=594 loops=1)
> Merge Cond: (("outer".partid = "inner".partid) AND
> ("outer".leadfree = "inner".leadfree) AND ("outer".duedate =
> "inner".duedate))
> -> Sort (cost=950.92..953.67 rows=1100 width=13) (actual
> time=40.871..43.071 rows=594 loops=1)
> Sort Key: b.partid, b.leadfree, b.duedate
> -> Subquery Scan b (cost=881.60..895.35 rows=1100
> width=13) (actual time=31.630..38.389 rows=594 loops=1)
> -> HashAggregate (cost=881.60..884.35
> rows=1100 width=13) (actual time=31.620..34.129 rows=594 loops=1)
> -> Seq Scan on
> stat_allocated_components (cost=0.00..799.11 rows=10998 width=13)
> (actual time=10.539..20.667 rows=2304 loops=1)
> Filter: (quantity < 0)
> -> Sort (cost=978.41..981.16 rows=1100 width=17) (actual
> time=68.568..76.751 rows=1975 loops=1)
> Sort Key: c.partid, c.leadfree, c.duedate
> -> Subquery Scan c (cost=909.09..922.84 rows=1100
> width=17) (actual time=31.546..57.932 rows=1975 loops=1)
> -> HashAggregate (cost=909.09..911.84
> rows=1100 width=17) (actual time=31.536..41.587 rows=1975 loops=1)
> -> Seq Scan on
> stat_allocated_components (cost=0.00..799.11 rows=10998 width=17)
> (actual time=9.755..19.349 rows=2304 loops=1)
> Filter: (quantity < 0)
> -> Index Scan using idx_statpartid on stat_allocated_components
> a (cost=0.00..13.53 rows=5 width=175) (actual time=0.023..5.454 rows=6
> loops=594)
> Index Cond: (a.partid = "outer".partid)
> -> Subquery Scan d (cost=1169.13..1264.30 rows=5438 width=41)
> (actual time=0.132..10.787 rows=758 loops=594)
> -> HashAggregate (cost=1169.13..1209.92 rows=5438 width=17)
> (actual time=0.119..4.112 rows=758 loops=594)
> -> Hash Join (cost=1.07..1031.49 rows=13764 width=17)
> (actual time=1.084..51.067 rows=2895 loops=1)
> Hash Cond: ("outer".leadstateid = "inner".leadstateid)
> -> Seq Scan on stat_allocated_components a
> (cost=0.00..767.89 rows=12489 width=20) (actual time=0.713..12.389
> rows=2627 loops=1)
> -> Hash (cost=1.06..1.06 rows=6 width=5) (actual
> time=0.236..0.236 rows=0 loops=1)
> -> Seq Scan on leadstatebools b
> (cost=0.00..1.06 rows=6 width=5) (actual time=0.054..0.204 rows=6 loops=1)
> Total runtime: 11917.190 ms