9.5alpha1 vs 9.4

From: "Mkrtchyan, Tigran" <tigran(dot)mkrtchyan(at)desy(dot)de>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: 9.5alpha1 vs 9.4
Date: 2015-07-05 11:10:51
Message-ID: 1897514812.2949286.1436094651680.JavaMail.zimbra@desy.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mkrtchyan, Tigran 2015-07-05 11:46:08 Re: 9.5alpha1 vs 9.4
Previous Message Marc Mamin 2015-07-03 16:15:44 Re: Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?