X-Original-To: pgsql-performance@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 8B5E4924E30 for ; Tue, 20 May 2003 09:28:58 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 43492-10 for ; Tue, 20 May 2003 09:28:54 -0400 (EDT) Received: from gate.solvo.ru (gate.solvo.ru [195.201.44.1]) by developer.postgresql.org (Postfix) with ESMTP id 143E0924CA9 for ; Tue, 20 May 2003 09:28:52 -0400 (EDT) Received: from grace.solvo.ru (grace.solvo.ru [195.201.44.100]) by gate.solvo.ru (8.8.8/8.8.8) with ESMTP id RAA08256; Tue, 20 May 2003 17:28:43 +0400 Received: from grace.solvo.ru (localhost [127.0.0.1]) by grace.solvo.ru (8.12.8/8.12.8) with ESMTP id h4KDShpD029388; Tue, 20 May 2003 17:28:43 +0400 Received: (from elf@localhost) by grace.solvo.ru (8.12.8/8.12.8/Submit) id h4KDSgNu029386; Tue, 20 May 2003 17:28:42 +0400 Date: Tue, 20 May 2003 17:28:42 +0400 From: Eugene Fokin To: Victor Yegorov Cc: pgsql-performance@postgresql.org Subject: Re: 7.3.2 vs 7.1.2 Message-ID: <20030520132842.GA29262@solvo.ru> References: <20030520122838.GA28654@solvo.ru> <20030520130913.GM1976@nordlb.lv> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable In-Reply-To: <20030520130913.GM1976@nordlb.lv> User-Agent: Mutt/1.4i X-Operating-System: Linux 2.4.19-1pe i686 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/193 X-Sequence-Number: 1972 On Tue, May 20, 2003 at 04:09:13PM +0300, Victor Yegorov wrote: =20 > Please, attach both: query and explain analyze results. > Results of: >=20 > =3D> select version(); >=20 > are welcomed too. Ok. btw, it works on 7.2.1 fine to me too (as 7.1.2). \d loadview: View "public.loadview" Column | Type | Modifiers=20 -------------------+--------------------------+----------- id | integer |=20 parent_load_id | integer |=20 name | character varying(10) |=20 code_id | integer |=20 rcn_id | integer |=20 loc_id | integer |=20 real_loc_id | integer |=20 dest_id | integer |=20 order_id | integer |=20 last_comment | character varying |=20 label | character varying(20) |=20 type | character varying(1) |=20 qty | integer |=20 qty_type | character varying(1) |=20 units | integer |=20 assigned | integer |=20 visible | boolean |=20 status | character varying(1) |=20 sort | integer |=20 dest_status | character varying(1) |=20 date_pour | date |=20 akciz_name | text |=20 is_ub | boolean |=20 is_toll | boolean |=20 has_receiving | boolean |=20 has_ub | boolean |=20 has_custom | boolean |=20 has_akciz | boolean |=20 owner_id | integer |=20 receive_type | character varying(1) |=20 region_units | integer |=20 msk_units | integer |=20 town_units | integer |=20 date_last_counted | timestamp with time zone |=20 counted_by | character varying(32) |=20 date_last_access | timestamp with time zone |=20 accessed_by | character varying(32) |=20 created | timestamp with time zone |=20 created_by | character varying(32) |=20 sku_name | character varying |=20 real_loc | integer |=20 loc_type | character varying |=20 View definition: SELECT l.id, l.parent_load_id, l.name, l.code_id, l.rcn_id= , l.loc_id, l.real_loc_id, l.dest_id, CASE WHEN (EXISTS (SELECT orders.id F= ROM orders WHERE (orders.id =3D l.order_id))) THEN l.order_id ELSE 0 END AS= order_id, (SELECT lc."comment" FROM load_comments lc WHERE (lc.id =3D l.la= st_comment_id)) AS last_comment, l.label, l."type", l.qty, l.qty_type, l.un= its, l.assigned, l.visible, l.status, l.sort, l.dest_status, r.date_pour, a= d.name AS akciz_name, l.is_ub, l.is_toll, l.has_receiving, l.has_ub, l.has_= custom, l.has_akciz, l.owner_id, l.receive_type, l.region_units, l.msk_unit= s, l.town_units, l.date_last_counted, l.counted_by, l.date_last_access, l.a= ccessed_by, l.created, l.created_by, (SELECT s.name FROM sku s, code_info c= WHERE ((s.id =3D c.sku_id) AND (c.id =3D l.code_id))) AS sku_name, l.real_= loc_id AS real_loc, (SELECT loc."type" FROM "location" loc WHERE (loc.id = =3D l.real_loc_id)) AS loc_type FROM (((loads l JOIN (SELECT rcn_details.id= , rcn_details.date_pour FROM rcn_details) r ON ((r.id =3D l.rcn_id))) LEFT = JOIN (SELECT min(akciz.id) AS id, akciz.rcn_id FROM akciz GROUP BY akciz.rc= n_id) ah ON ((ah.rcn_id =3D l.rcn_id))) LEFT JOIN (SELECT max((akciz_detail= s.name)::text) AS name, akciz_details.akciz_id FROM akciz_details GROUP BY = akciz_details.akciz_id) ad ON ((ad.akciz_id =3D ah.id))); 7.2.1: select version (): "PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96" explain analyze select count(*) from loadview: NOTICE: QUERY PLAN: Aggregate (cost=3D49464.29..49464.29 rows=3D1 width=3D20) (actual time=3D4= 823.05..4823.05 rows=3D1 loops=3D1) -> Merge Join (cost=3D36149.36..47306.99 rows=3D862919 width=3D20) (act= ual time=3D4081.67..4699.48 rows=3D147281 loops=3D1) -> Sort (cost=3D35013.94..35013.94 rows=3D147281 width=3D16) (act= ual time=3D3851.65..3919.07 rows=3D147281 loops=3D1) -> Merge Join (cost=3D1098.11..22371.18 rows=3D147281 width= =3D16) (actual time=3D196.80..3001.89 rows=3D147281 loops=3D1) -> Merge Join (cost=3D0.00..19885.60 rows=3D147281 wi= dth=3D8) (actual time=3D0.08..2059.89 rows=3D147281 loops=3D1) -> Index Scan using load_rcn_id_idx on loads l = (cost=3D0.00..17026.36 rows=3D147281 width=3D4) (actual time=3D0.04..786.13= rows=3D147281 loops=3D1) -> Index Scan using rcn_detail_idx on rcn_detail= s (cost=3D0.00..618.30 rows=3D12692 width=3D4) (actual time=3D0.03..510.13= rows=3D151332 loops=3D1) -> Sort (cost=3D1098.11..1098.11 rows=3D1161 width=3D= 8) (actual time=3D196.68..273.26 rows=3D140535 loops=3D1) -> Subquery Scan ah (cost=3D980.95..1039.00 row= s=3D1161 width=3D8) (actual time=3D73.79..167.89 rows=3D11497 loops=3D1) -> Aggregate (cost=3D980.95..1039.00 rows= =3D1161 width=3D8) (actual time=3D73.78..145.90 rows=3D11497 loops=3D1) -> Group (cost=3D980.95..1009.98 ro= ws=3D11610 width=3D8) (actual time=3D73.76..115.53 rows=3D11610 loops=3D1) -> Sort (cost=3D980.95..980.9= 5 rows=3D11610 width=3D8) (actual time=3D73.75..78.99 rows=3D11610 loops=3D= 1) -> Seq Scan on akciz (c= ost=3D0.00..197.10 rows=3D11610 width=3D8) (actual time=3D0.01..26.24 rows= =3D11610 loops=3D1) -> Sort (cost=3D1135.43..1135.43 rows=3D1172 width=3D15) (actual = time=3D229.97..308.41 rows=3D140648 loops=3D1) -> Subquery Scan ad (cost=3D1017.11..1075.70 rows=3D1172 wi= dth=3D15) (actual time=3D94.52..200.64 rows=3D11610 loops=3D1) -> Aggregate (cost=3D1017.11..1075.70 rows=3D1172 wid= th=3D15) (actual time=3D94.51..179.57 rows=3D11610 loops=3D1) -> Group (cost=3D1017.11..1046.40 rows=3D11718 = width=3D15) (actual time=3D94.49..135.00 rows=3D11718 loops=3D1) -> Sort (cost=3D1017.11..1017.11 rows=3D1= 1718 width=3D15) (actual time=3D94.47..101.80 rows=3D11718 loops=3D1) -> Seq Scan on akciz_details (cost= =3D0.00..225.18 rows=3D11718 width=3D15) (actual time=3D0.03..30.11 rows=3D= 11718 loops=3D1) Total runtime: 4878.56 msec 7.3.2: select version(): "PostgreSQL 7.3.2 on i386-redhat-linux-gnu, compiled by GCC i386-red= hat-linux-gcc (GCC) 3.2.2 20030213 (Red Hat Linux 8.0 3.2.2-1)" Also, I've tried 7.3.2 version binaries from PostgreSQL site for RH7= 3. And I've got the same result. explain analyze select count(*) from loadview: = QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------ Aggregate (cost=3D57642.48..57642.48 rows=3D1 width=3D233) (actual time= =3D43799.03..43799.03 rows=3D1 loops=3D1) -> Subquery Scan loadview (cost=3D43956.42..55485.18 rows=3D862919 wid= th=3D233) (actual time=3D28013.35..43638.75 rows=3D147281 loops=3D1) -> Merge Join (cost=3D43956.42..55485.18 rows=3D862919 width=3D2= 33) (actual time=3D28013.35..43409.03 rows=3D147281 loops=3D1) Merge Cond: ("outer".id =3D "inner".akciz_id) -> Sort (cost=3D42797.70..43165.90 rows=3D147281 width=3D1= 97) (actual time=3D27785.80..28126.86 rows=3D147281 loops=3D1) Sort Key: ah.id -> Merge Join (cost=3D1115.13..22038.07 rows=3D14728= 1 width=3D197) (actual time=3D133.98..14205.66 rows=3D147281 loops=3D1) Merge Cond: ("outer".rcn_id =3D "inner".rcn_id) -> Merge Join (cost=3D0.00..19524.78 rows=3D14= 7281 width=3D189) (actual time=3D0.14..9419.68 rows=3D147281 loops=3D1) Merge Cond: ("outer".rcn_id =3D "inner".id) -> Index Scan using load_rcn_id_idx on lo= ads l (cost=3D0.00..16659.18 rows=3D147281 width=3D181) (actual time=3D0.0= 7..4486.76 rows=3D147281 loops=3D1) -> Index Scan using rcn_detail_idx on rcn= _details (cost=3D0.00..624.96 rows=3D12692 width=3D8) (actual time=3D0.02.= .587.84 rows=3D151332 loops=3D1) -> Sort (cost=3D1115.13..1118.03 rows=3D1161 w= idth=3D8) (actual time=3D133.74..214.17 rows=3D140535 loops=3D1) Sort Key: ah.rcn_id -> Subquery Scan ah (cost=3D968.95..1056= .03 rows=3D1161 width=3D8) (actual time=3D46.03..115.21 rows=3D11497 loops= =3D1) -> Aggregate (cost=3D968.95..1056.= 03 rows=3D1161 width=3D8) (actual time=3D46.02..100.01 rows=3D11497 loops= =3D1) -> Group (cost=3D968.95..102= 7.00 rows=3D11610 width=3D8) (actual time=3D46.00..76.80 rows=3D11610 loops= =3D1) -> Sort (cost=3D968.95= ..997.98 rows=3D11610 width=3D8) (actual time=3D45.99..50.45 rows=3D11610 l= oops=3D1) Sort Key: rcn_id -> Seq Scan on ak= ciz (cost=3D0.00..185.10 rows=3D11610 width=3D8) (actual time=3D0.01..19.0= 9 rows=3D11610 loops=3D1) -> Sort (cost=3D1158.72..1161.65 rows=3D1172 width=3D15) (= actual time=3D227.16..332.79 rows=3D140648 loops=3D1) Sort Key: ad.akciz_id -> Subquery Scan ad (cost=3D1011.11..1098.99 rows=3D= 1172 width=3D15) (actual time=3D80.77..188.32 rows=3D11610 loops=3D1) -> Aggregate (cost=3D1011.11..1098.99 rows=3D1= 172 width=3D15) (actual time=3D80.76..158.60 rows=3D11610 loops=3D1) -> Group (cost=3D1011.11..1069.70 rows= =3D11718 width=3D15) (actual time=3D80.73..124.73 rows=3D11718 loops=3D1) -> Sort (cost=3D1011.11..1040.40 r= ows=3D11718 width=3D15) (actual time=3D80.71..88.88 rows=3D11718 loops=3D1) Sort Key: akciz_id -> Seq Scan on akciz_details = (cost=3D0.00..219.18 rows=3D11718 width=3D15) (actual time=3D0.03..28.57 r= ows=3D11718 loops=3D1) SubPlan -> Index Scan using orders_id_idx on orders (cost=3D0.00= ..5.92 rows=3D1 width=3D4) (actual time=3D0.01..0.01 rows=3D1 loops=3D14728= 1) Index Cond: (id =3D $0) -> Index Scan using load_comments_id_idx on load_comments= lc (cost=3D0.00..5.90 rows=3D1 width=3D10) (actual time=3D0.01..0.01 rows= =3D0 loops=3D147281) Index Cond: (id =3D $1) -> Nested Loop (cost=3D0.00..11.08 rows=3D1 width=3D59) = (actual time=3D0.02..0.03 rows=3D1 loops=3D147281) -> Index Scan using code_id_idx on code_info c (co= st=3D0.00..5.07 rows=3D1 width=3D4) (actual time=3D0.01..0.01 rows=3D1 loop= s=3D147281) Index Cond: (id =3D $2) -> Index Scan using sku_id_idx on sku s (cost=3D0.= 00..6.00 rows=3D1 width=3D55) (actual time=3D0.01..0.01 rows=3D1 loops=3D14= 7281) Index Cond: (s.id =3D "outer".sku_id) -> Index Scan using loc_g_id_idx on "location" loc (cost= =3D0.00..5.98 rows=3D1 width=3D5) (actual time=3D0.01..0.01 rows=3D1 loops= =3D147281) Index Cond: (id =3D $3) Total runtime: 43825.44 msec (41 rows) --=20 Eugene Fokin SOLVO Ltd. Company