Re: 7.3.2 vs 7.1.2 - Mailing list pgsql-performance

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,
(SELECTlc."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_idAS real_loc, (SELECT loc."type" FROM "location" loc WHERE (loc.id = l.real_loc_id)) AS loc_type FROM
(((loadsl JOIN (SELECT rcn_details.id, rcn_details.date_pour FROM rcn_details) r ON ((r.id = l.rcn_id))) LEFT JOIN
(SELECTmin(akciz.id) AS id, akciz.rcn_id FROM akciz GROUP BY akciz.rcn_id) ah ON ((ah.rcn_id = l.rcn_id))) LEFT JOIN
(SELECTmax((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)
(actualtime=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)
(actualtime=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=11497loops=1) 
                                ->  Aggregate  (cost=980.95..1039.00 rows=1161 width=8) (actual time=73.78..145.90
rows=11497loops=1) 
                                      ->  Group  (cost=980.95..1009.98 rows=11610 width=8) (actual time=73.76..115.53
rows=11610loops=1) 
                                            ->  Sort  (cost=980.95..980.95 rows=11610 width=8) (actual
time=73.75..78.99rows=11610 loops=1) 
                                                  ->  Seq Scan on akciz  (cost=0.00..197.10 rows=11610 width=8) (actual
time=0.01..26.24rows=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=11718loops=1) 
                                      ->  Seq Scan on akciz_details  (cost=0.00..225.18 rows=11718 width=15) (actual
time=0.03..30.11rows=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
Linux8.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=147281loops=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=147281loops=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=147281loops=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.21rows=11497 loops=1) 
                                       ->  Aggregate  (cost=968.95..1056.03 rows=1161 width=8) (actual
time=46.02..100.01rows=11497 loops=1) 
                                             ->  Group  (cost=968.95..1027.00 rows=11610 width=8) (actual
time=46.00..76.80rows=11610 loops=1) 
                                                   ->  Sort  (cost=968.95..997.98 rows=11610 width=8) (actual
time=45.99..50.45rows=11610 loops=1) 
                                                         Sort Key: rcn_id
                                                         ->  Seq Scan on akciz  (cost=0.00..185.10 rows=11610 width=8)
(actualtime=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=11610loops=1) 
                           ->  Aggregate  (cost=1011.11..1098.99 rows=1172 width=15) (actual time=80.76..158.60
rows=11610loops=1) 
                                 ->  Group  (cost=1011.11..1069.70 rows=11718 width=15) (actual time=80.73..124.73
rows=11718loops=1) 
                                       ->  Sort  (cost=1011.11..1040.40 rows=11718 width=15) (actual time=80.71..88.88
rows=11718loops=1) 
                                             Sort Key: akciz_id
                                             ->  Seq Scan on akciz_details  (cost=0.00..219.18 rows=11718 width=15)
(actualtime=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=1loops=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)
(actualtime=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.01rows=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.01rows=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.01rows=1 loops=147281) 
                       Index Cond: (id = $3)
 Total runtime: 43825.44 msec
(41 rows)

--
Eugene Fokin
SOLVO Ltd. Company