explain problem
От | Sim Zacks |
---|---|
Тема | explain problem |
Дата | |
Msg-id | f29gqp$25mt$1@news.hub.org обсуждение исходный текст |
Ответы |
IGNORE Re: explain problem
(Sim Zacks <sim@compulab.co.il>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: