Re: 7.3.2 vs 7.1.2

On Tue, May 20, 2003 at 04:09:13PM +0300, Victor Yegorov wrote:

> Please, attach both: query and explain analyze results.
> Results of:
>
> => select version();
>
> 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
-------------------+--------------------------+-----------
id | integer |
parent_load_id | integer |
name | character varying(10) |
code_id | integer |
rcn_id | integer |
loc_id | integer |
real_loc_id | integer |
dest_id | integer |
order_id | integer |
last_comment | character varying |
label | character varying(20) |
type | character varying(1) |
qty | integer |
qty_type | character varying(1) |
units | integer |
assigned | integer |
visible | boolean |
status | character varying(1) |
sort | integer |
dest_status | character varying(1) |
date_pour | date |
akciz_name | text |
is_ub | boolean |
is_toll | boolean |
has_receiving | boolean |
has_ub | boolean |
has_custom | boolean |
has_akciz | boolean |
owner_id | integer |
receive_type | character varying(1) |
region_units | integer |
msk_units | integer |
town_units | integer |
date_last_counted | timestamp with time zone |
counted_by | character varying(32) |
date_last_access | timestamp with time zone |
accessed_by | character varying(32) |
created | timestamp with time zone |
created_by | character varying(32) |
sku_name | character varying |
real_loc | integer |
loc_type | character varying |
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 FROM orders WHERE (orders.id = l.order_id))) THEN l.order_id ELSE 0 END AS order_id, (SELECT lc."comment" FROM load_comments lc WHERE (lc.id = l.last_comment_id)) AS last_comment, l.label, l."type", l.qty, l.qty_type, l.units, l.assigned, l.visible, l.status, l.sort, l.dest_status, r.date_pour, ad.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_units, l.town_units, l.date_last_counted, l.counted_by, l.date_last_access, l.accessed_by, l.created, l.created_by, (SELECT s.name FROM sku s, code_info c WHERE ((s.id = c.sku_id) AND (c.id = l.code_id))) AS sku_name, l.real_loc_id AS real_loc, (SELECT loc."type" FROM "location" loc WHERE (loc.id = 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 = l.rcn_id))) LEFT JOIN (SELECT min(akciz.id) AS id, akciz.rcn_id FROM akciz GROUP BY akciz.rcn_id) ah ON ((ah.rcn_id = l.rcn_id))) LEFT JOIN (SELECT max((akciz_details.name)::text) AS name, akciz_details.akciz_id FROM akciz_details GROUP BY akciz_details.akciz_id) ad ON ((ad.akciz_id = 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=49464.29..49464.29 rows=1 width=20) (actual time=4823.05..4823.05 rows=1 loops=1)
-> Merge Join (cost=36149.36..47306.99 rows=862919 width=20) (actual time=4081.67..4699.48 rows=147281 loops=1)
-> Sort (cost=35013.94..35013.94 rows=147281 width=16) (actual time=3851.65..3919.07 rows=147281 loops=1)
-> Merge Join (cost=1098.11..22371.18 rows=147281 width=16) (actual time=196.80..3001.89 rows=147281 loops=1)
-> Merge Join (cost=0.00..19885.60 rows=147281 width=8) (actual time=0.08..2059.89 rows=147281 loops=1)
-> Index Scan using load_rcn_id_idx on loads l (cost=0.00..17026.36 rows=147281 width=4) (actual time=0.04..786.13 rows=147281 loops=1)
-> Index Scan using rcn_detail_idx on rcn_details (cost=0.00..618.30 rows=12692 width=4) (actual time=0.03..510.13 rows=151332 loops=1)
-> Sort (cost=1098.11..1098.11 rows=1161 width=8) (actual time=196.68..273.26 rows=140535 loops=1)
-> Subquery Scan ah (cost=980.95..1039.00 rows=1161 width=8) (actual time=73.79..167.89 rows=11497 loops=1)
-> Aggregate (cost=980.95..1039.00 rows=1161 width=8) (actual time=73.78..145.90 rows=11497 loops=1)
-> Group (cost=980.95..1009.98 rows=11610 width=8) (actual time=73.76..115.53 rows=11610 loops=1)
-> Sort (cost=980.95..980.95 rows=11610 width=8) (actual time=73.75..78.99 rows=11610 loops=1)
-> Seq Scan on akciz (cost=0.00..197.10 rows=11610 width=8) (actual time=0.01..26.24 rows=11610 loops=1)
-> Sort (cost=1135.43..1135.43 rows=1172 width=15) (actual time=229.97..308.41 rows=140648 loops=1)
-> Subquery Scan ad (cost=1017.11..1075.70 rows=1172 width=15) (actual time=94.52..200.64 rows=11610 loops=1)
-> Aggregate (cost=1017.11..1075.70 rows=1172 width=15) (actual time=94.51..179.57 rows=11610 loops=1)
-> Group (cost=1017.11..1046.40 rows=11718 width=15) (actual time=94.49..135.00 rows=11718 loops=1)
-> Sort (cost=1017.11..1017.11 rows=11718 width=15) (actual time=94.47..101.80 rows=11718 loops=1)
-> Seq Scan on akciz_details (cost=0.00..225.18 rows=11718 width=15) (actual time=0.03..30.11 rows=11718 loops=1)
Total runtime: 4878.56 msec

7.3.2:
select version():
"PostgreSQL 7.3.2 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-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 RH73.
And I've got the same result.

explain analyze select count(*) from loadview:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=57642.48..57642.48 rows=1 width=233) (actual time=43799.03..43799.03 rows=1 loops=1)
-> Subquery Scan loadview (cost=43956.42..55485.18 rows=862919 width=233) (actual time=28013.35..43638.75 rows=147281 loops=1)
-> Merge Join (cost=43956.42..55485.18 rows=862919 width=233) (actual time=28013.35..43409.03 rows=147281 loops=1)
Merge Cond: ("outer".id = "inner".akciz_id)
-> Sort (cost=42797.70..43165.90 rows=147281 width=197) (actual time=27785.80..28126.86 rows=147281 loops=1)
Sort Key: ah.id
-> Merge Join (cost=1115.13..22038.07 rows=147281 width=197) (actual time=133.98..14205.66 rows=147281 loops=1)
Merge Cond: ("outer".rcn_id = "inner".rcn_id)
-> Merge Join (cost=0.00..19524.78 rows=147281 width=189) (actual time=0.14..9419.68 rows=147281 loops=1)
Merge Cond: ("outer".rcn_id = "inner".id)
-> Index Scan using load_rcn_id_idx on loads l (cost=0.00..16659.18 rows=147281 width=181) (actual time=0.07..4486.76 rows=147281 loops=1)
-> Index Scan using rcn_detail_idx on rcn_details (cost=0.00..624.96 rows=12692 width=8) (actual time=0.02..587.84 rows=151332 loops=1)
-> Sort (cost=1115.13..1118.03 rows=1161 width=8) (actual time=133.74..214.17 rows=140535 loops=1)
Sort Key: ah.rcn_id
-> Subquery Scan ah (cost=968.95..1056.03 rows=1161 width=8) (actual time=46.03..115.21 rows=11497 loops=1)
-> Aggregate (cost=968.95..1056.03 rows=1161 width=8) (actual time=46.02..100.01 rows=11497 loops=1)
-> Group (cost=968.95..1027.00 rows=11610 width=8) (actual time=46.00..76.80 rows=11610 loops=1)
-> Sort (cost=968.95..997.98 rows=11610 width=8) (actual time=45.99..50.45 rows=11610 loops=1)
Sort Key: rcn_id
-> Seq Scan on akciz (cost=0.00..185.10 rows=11610 width=8) (actual time=0.01..19.09 rows=11610 loops=1)
-> Sort (cost=1158.72..1161.65 rows=1172 width=15) (actual time=227.16..332.79 rows=140648 loops=1)
Sort Key: ad.akciz_id
-> Subquery Scan ad (cost=1011.11..1098.99 rows=1172 width=15) (actual time=80.77..188.32 rows=11610 loops=1)
-> Aggregate (cost=1011.11..1098.99 rows=1172 width=15) (actual time=80.76..158.60 rows=11610 loops=1)
-> Group (cost=1011.11..1069.70 rows=11718 width=15) (actual time=80.73..124.73 rows=11718 loops=1)
-> Sort (cost=1011.11..1040.40 rows=11718 width=15) (actual time=80.71..88.88 rows=11718 loops=1)
Sort Key: akciz_id
-> Seq Scan on akciz_details (cost=0.00..219.18 rows=11718 width=15) (actual time=0.03..28.57 rows=11718 loops=1)
SubPlan
-> Index Scan using orders_id_idx on orders (cost=0.00..5.92 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=147281)
Index Cond: (id = $0)
-> Index Scan using load_comments_id_idx on load_comments lc (cost=0.00..5.90 rows=1 width=10) (actual time=0.01..0.01 rows=0 loops=147281)
Index Cond: (id = $1)
-> Nested Loop (cost=0.00..11.08 rows=1 width=59) (actual time=0.02..0.03 rows=1 loops=147281)
-> Index Scan using code_id_idx on code_info c (cost=0.00..5.07 rows=1 width=4) (actual time=0.01..0.01 rows=1 loops=147281)
Index Cond: (id = $2)
-> Index Scan using sku_id_idx on sku s (cost=0.00..6.00 rows=1 width=55) (actual time=0.01..0.01 rows=1 loops=147281)
Index Cond: (s.id = "outer".sku_id)
-> Index Scan using loc_g_id_idx on "location" loc (cost=0.00..5.98 rows=1 width=5) (actual time=0.01..0.01 rows=1 loops=147281)
Index Cond: (id = $3)
Total runtime: 43825.44 msec
(41 rows)

--
Eugene Fokin
SOLVO Ltd. Company