0

I have a production vm with centos 7.6 under hyper-v and a windows 10 laptop as a dev machine and both are running postgresql 9.6, on my laptop 10k UPDATE queries only take 9 seconds while on the server they take 320 seconds

the laptop has an ssd while the server has a 7200 rpm disk, still I think the difference is way too high to be just about the ssd

this is the table

CREATE TABLE "public"."test_table" (
  "id" int4 NOT NULL DEFAULT nextval('test_table_id_seq'::regclass),
  "field1" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
  "field2" int4 NOT NULL,
  "fk" int4,
);

CREATE INDEX "test_table_field1_3e4d8ff6" ON "public"."test_table" USING btree (
  "field1" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "test_table_field1_3e4d8ff6_like" ON "public"."test_table" USING btree (
  "field1" COLLATE "pg_catalog"."default" "pg_catalog"."varchar_pattern_ops" ASC NULLS LAST
);
CREATE INDEX "test_table_field2_aff278f4" ON "public"."test_table" USING btree (
  "field2" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "test_table_fk_8a3cfc7c" ON "public"."test_table" USING btree (
  "fk" "pg_catalog"."int4_ops" ASC NULLS LAST
);

ALTER TABLE "public"."test_table" ADD CONSTRAINT "test_table_pkey" PRIMARY KEY ("id");

ALTER TABLE "public"."test_table" ADD CONSTRAINT "test_table_fk_8a3cfc7c_fk_table1" FOREIGN KEY ("fk") REFERENCES "public"."table1" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED;

and the query that is repeated 10k times but with different data each time

UPDATE "test_table" 
SET "field1" = 'ABCD',
"field2" = '53',
"fk" = 5 
WHERE "test_table"."id" = 1

and explain (analyze, buffers)

Update on test_table  (cost=0.29..8.30 rows=1 width=236) (actual time=0.393..0.393 rows=0 loops=1)
  Buffers: shared hit=31 dirtied=11
  ->  Index Scan using test_table_pkey on test_table (cost=0.29..8.30 rows=1 width=236) (actual time=0.022..0.023 rows=1 loops=1)
        Index Cond: (id = 1)
        Buffers: shared hit=3
Planning time: 0.097 ms
Execution time: 0.419 ms
  • Hey there @brandon. So what is your question exactly? – Anthony Genovese Apr 15 at 21:38
  • half a millisecond doesn't really sound slow to me. – a_horse_with_no_name Apr 16 at 5:36
  • @a_horse_with_no_name but when I run 10k of them the difference between the server and my local machine is staggering, I've also tried with another local machine with windows 10 and a 7200rpm disk and the queries are just slightly slower than on the ssd, so I'm wondering if it's related to the server being an hyper-v vm – Brandon Apr 16 at 9:20
  • The show us the execution plan from the slow server. – a_horse_with_no_name Apr 16 at 9:21
  • "still I think the difference is way too high to be just about the ssd". When the evidence contradicts your opinion, you should change your opinion. What happens if you test with synchronous_commit off? – jjanes Apr 17 at 0:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.