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