pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Eugene Fokin <elf@solvo.ru>
To: Victor Yegorov <viktors.jegorovs@nordlb.lv>
Cc: pgsql-performance@postgresql.org
Subject: Re: 7.3.2 vs 7.1.2
Date: Tue, 20 May 2003 17:28:42 +0400
Message-ID: <20030520132842.GA29262@solvo.ru> (raw)
In-Reply-To: <20030520130913.GM1976@nordlb.lv>
References: <20030520122838.GA28654@solvo.ru>
	<20030520130913.GM1976@nordlb.lv>

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



view thread (14+ messages)  latest in thread

Message-ID: <20030520132842.GA29262@solvo.ru>
Permalink:  ../20030520132842.GA29262@solvo.ru/
Also on:    postgresql.org/message-id/20030520132842.GA29262@solvo.ru

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: elf@solvo.ru, viktors.jegorovs@nordlb.lv
  Subject: Re: 7.3.2 vs 7.1.2
  In-Reply-To: <20030520132842.GA29262@solvo.ru>
  References: <20030520122838.GA28654@solvo.ru> <20030520130913.GM1976@nordlb.lv> <20030520132842.GA29262@solvo.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox