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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
