0

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?

closed as off-topic by Evan Carroll, Vérace, LowlyDBA, Philᵀᴹ, Michael Green Jun 10 '18 at 6:11

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Evan Carroll, Vérace, LowlyDBA, Philᵀᴹ, Michael Green
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I'm voting to close this because it's too localized. I would also suggest you take a look at PostGIS's raster types to store pixels, which are far more useful than bytea for your work load. – Evan Carroll Jun 6 '18 at 17:17
  • Thanks for your suggestion, I'll try to look on PostGIS docs. In my case pixels can have additional properties, different count of bytes per pixel, and they aren't just "image as is", convertion of this data to real PNG image is made by specialized algorithm, so, I came to decision to use raw byte array. – MihanEntalpo Jun 7 '18 at 4:55
  • A raster isn't an image, though it can be rendered as one. PostGIS will support doing what you need. – Evan Carroll Jun 7 '18 at 6:32
0

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.

  • 3
    That's why you should always add the output of explain (analyze, buffers) to any performance related question. – a_horse_with_no_name Jun 6 '18 at 12:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.