Slow updates of PostgreSql ByteA fields with just 1KB of data [closed]

Question

TL;DR: Inserting or updating table, with putting 1024 bytes into bytea column works quite slow.

I have a PostgreSql 9.5.12 installation on my Desktop Linux Mint 18, with SSD drive, Core-i5, and 16GB Ram.

I've created table with the folowing structure:

CREATE TABLE "cells" (      
  "x" integer NOT NULL DEFAULT 0,
  "y" integer NOT NULL DEFAULT 0,
  "palette_id" integer DEFAULT 1,
  "image" bytea NULL      
);

It represents a "cells" on a 2D-field with 32x32 pixels image on it. "image" column always contain 1024 bytes, each byte is a color of one of the 32x32 pixels (each color is represended by a single byte).

So, every time, when I'm changing image and write it back to table, it takes about 450 milliseconds (almost a half of a second!).

I'm writing it with query like this (using php PDO):

UPDATE cells 
SET image=decode('00000000000000000000000000000000000000000000000000000000000000000000002a2a4b6b6c4b4a2a2a4b00002a222a000000002222000000002200000000002a4b2a4b95b6956c2a4b95b6954b2a4b6c956c4b2a2a6b6c4b2a222a2a00004a4a4a2a4a4b4b4b4a222a4b734a2a2a2a4a4b4b2a222a4b4b2a2a222a4b2a7151312a2a2a2a5252292a2a5229312a2a2a2a5252292a2a5231312a2a2a2a317172524b2a2a4b4b4b4a2a2a4a4b4b2a2a2a4a4b4b2a2a2a2a4b4b2a2a2a2a4b71738d6d4b236d8e8e6d23236d8e6e6d23236d6e6e4c23236d6e6d4423236d6e7193aeaf4b4b8eb0b08643438eb0b08643438eb0876643438e8f8766234366877193b6af4b4baed8d08e4b4baed0d08e434baed0b08e434baed0b08e434366877193b6af6d6cafd8d0af6c6cafd0d0af6c6cafd0b08e646caed0af8e444466877173aeb08f8fafb0b0af8787afb0b08786868faf8f8766668f8f8787666666677173aed0b0b0b0b0b0b0b0b0a8a8a8a8a88787878787878787878787878767667193b6d0d0d0d0b0b0b0b0b0b0b0b0b0b0b0b0afafafafafa7a78787878767667193b6d0d0d0d0d0d0d0d0d0b0afafafafafafafafafaf8fafafafafaf8767667193b6d0d0d0d0d0d0d0d0d0afae6d6dafaf8d8dafaf6d6d8fafafafaf8767667193b6d0d0d0d0d0d0d0d0b0af8d4b4c8eaf6d6c8f8f6c648eafafafaf8767677193b6d0d0d0d0d0d0d0d0af8e6c23438eaf6d6daf8f6c648eafafafaf8767677193b6d0d0d0afaeaeaeaf8e6d4322438eaf6d6dafaf6d648eafafafaf8767677193b6d0d0af6d4c4b4b6c6c432222438eaf6d6dafaf6d6c8eafafafaf8767677193b6d0d0af4c2222222343222222438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0af6c2222222343222222438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0af6d4c4b4b6c6c432222438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0d0afaeaeaeaf8e6d4322438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0d0d0d0d0d0d0af8e6c234b8eaf6d6dafaf6d6c8eb0b0afaf8787677193b6d0d0d0d0d0d0d0d0d0af8d4b4cafaf6d6dafaf6c6c8eb0b0afaf8787677193b6d0d0d0d0d0d0d0d0d0afae6d6dafaf8d8dafaf8d6dafb0afafafa787677193b6d0d0d0d0d0d0d0d0d0d0afafafafb0afafafafafafafb0afafafa787677193b6d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0b0b0b0b0b0b0afafafa7876700b5b6d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0b0b0b0b0b0b0afafafaf876700f8d8d0b0b0b0b0b0b0b0b0afaeaeaeaeaeaeaeafd0b0b0b0b0b0afaf87876700f8d8b0906868686868688f9493937373737394b7d0d0b0b0b0b0af8787876700000000686848484848486e9391717171717193b6d0d0b0b0b0b0af87676700', 'hex') 
WHERE id=2941

I've also tried to use the E'\\x escaping instead of "decode" function.

If I run this query from the psql console, it takes about 0.25 second, which is faster whan php's query, but not dramatically.

I've googled for "bytea performance issues" and found, that people have problems with importing like 100MB of data into a table column, not the 1kb.

Also, for comparision, in other table I'm inserting an array of 256 char items (which are 9 bytes long each), and it takes about 0.00156 second.

So:

1024B to bytea takes 0.25 seconds

2304B to char[9][] array takes 0.00156 seconds

How could it be? What should I do? Should I use text datatype instead of bytea? Or maybe an array of bytes would be more efficient?

Answer 1

To my shame, the problem was not related to bytea data type at all.

It was just absence of the primary key index in my table, and as table grow to several hundreds of thousands of records, finding record by id became too slow.

Now, when I've created primary key index, inserting of the data takes about 0.0001 seconds.