Date: Sun, 5 Jul 2015 13:10:51 +0200 (CEST)
From: "Mkrtchyan, Tigran" <tigran.mkrtchyan@desy.de>
To: pgsql-performance <pgsql-performance@postgresql.org>
Message-ID: <1897514812.2949286.1436094651680.JavaMail.zimbra@desy.de>
Subject: 9.5alpha1 vs 9.4
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Thread-Topic: 9.5alpha1 vs 9.4
Thread-Index: ScEC8YMiKb2jo9sBKotS2mk4jI37WA==
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


Hi,

today I have update my test system to 9.5alpha1.
Most of the operations are ok, except delete.
I get ~1000 times slower!


chimera=# SELECT
  (total_time / 1000 )::numeric(10,2) as total_secs,
  (total_time/calls)::numeric(10,2) as average_time_ms, calls,
  query
FROM pg_stat_statements where userid = 16384
ORDER BY 1 DESC
LIMIT 10;
 total_secs | average_time_ms | calls |                                                                                  query                                                   
                               
------------+-----------------+-------+------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------
     255.88 |          566.11 |   452 | DELETE FROM t_inodes WHERE ipnfsid=$1 AND inlink = ?
       0.13 |            0.13 |  1006 | insert into t_dirs (iparent, iname, ipnfsid)  (select $1 as iparent, $2 as iname, $3 as ipnfsid  where not exists (select ? from t_dirs where iparent=$4 and iname=$5))
       0.11 |            0.02 |  6265 | SELECT isize,inlink,itype,imode,iuid,igid,iatime,ictime,imtime,icrtime,igeneration FROM t_inodes WHERE ipnfsid=$1
       0.03 |            0.03 |  1002 | INSERT INTO t_inodes VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
       0.02 |            0.02 |  1002 | UPDATE t_inodes SET inlink=inlink +$1,imtime=$2,ictime=$3,igeneration=igeneration+? WHERE ipnfsid=$4
       0.02 |            0.03 |   905 | UPDATE t_inodes SET inlink=inlink -$1,imtime=$2,ictime=$3,igeneration=igeneration+? WHERE ipnfsid=$4
       0.02 |            0.01 |  2000 | SELECT ilocation,ipriority,ictime,iatime  FROM t_locationinfo WHERE itype=$1 AND ipnfsid=$2 AND istate=? ORDER BY ipriority DESC
       0.01 |            0.01 |   906 | SELECT ipnfsid FROM t_dirs WHERE iname=$1 AND iparent=$2
       0.01 |            0.01 |   453 | DELETE FROM t_dirs WHERE iname=$1 AND iparent=$2


chimera=# \d t_inodes
                     Table "public.t_inodes"
   Column    |           Type           |       Modifiers        
-------------+--------------------------+------------------------
 ipnfsid     | character varying(36)    | not null
 itype       | integer                  | not null
 imode       | integer                  | not null
 inlink      | integer                  | not null
 iuid        | integer                  | not null
 igid        | integer                  | not null
 isize       | bigint                   | not null
 iio         | integer                  | not null
 ictime      | timestamp with time zone | not null
 iatime      | timestamp with time zone | not null
 imtime      | timestamp with time zone | not null
 icrtime     | timestamp with time zone | not null default now()
 igeneration | bigint                   | not null default 0
Indexes:
    "t_inodes_pkey" PRIMARY KEY, btree (ipnfsid)
Referenced by:
    TABLE "t_access_latency" CONSTRAINT "t_access_latency_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_acl" CONSTRAINT "t_acl_fkey" FOREIGN KEY (rs_id) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_dirs" CONSTRAINT "t_dirs_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid)
    TABLE "t_inodes_checksum" CONSTRAINT "t_inodes_checksum_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_inodes_data" CONSTRAINT "t_inodes_data_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_level_1" CONSTRAINT "t_level_1_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_level_2" CONSTRAINT "t_level_2_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_level_3" CONSTRAINT "t_level_3_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_level_4" CONSTRAINT "t_level_4_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_level_5" CONSTRAINT "t_level_5_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_level_6" CONSTRAINT "t_level_6_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_level_7" CONSTRAINT "t_level_7_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_locationinfo" CONSTRAINT "t_locationinfo_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_retention_policy" CONSTRAINT "t_retention_policy_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_storageinfo" CONSTRAINT "t_storageinfo_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid) ON DELETE CASCADE
    TABLE "t_tags" CONSTRAINT "t_tags_ipnfsid_fkey" FOREIGN KEY (ipnfsid) REFERENCES t_inodes(ipnfsid)
Triggers:
    tgr_locationinfo_trash BEFORE DELETE ON t_inodes FOR EACH ROW EXECUTE PROCEDURE f_locationinfo2trash()


Any ideas?

  Tigran.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance