Get column with text datatype slow query

Question

I have the following table:

create table tbl_img
(
  id int,
  col_img text
);

I have stored image in the column col_img.

When I try to get data:

select t1.id,t1.col1,t2.col_img
from tbl_test t1
left join tbl_img t2 on t1.img_id = t2.id;

Taking more than 1 minute time to get data. If I remove t2.col_img from column list it will give result within milliseconds.

Is there any work around to improve performance?

Maybe find a way without using the join? – Carl Kristensen Sep 07 '20 at 13:01 — Carl Kristensen, Sep 07 '20 at 13:01

score 1 · Accepted Answer · answered Sep 07 '20 at 13:00

If you remove t2.col_img from the SELECT list, PostgreSQL is smart enough to know that it can skip the left join, since id on tbl_img has a unique constraint.

With t2.col_img, PostgreSQL actually has to perform the join, so it is much more expensive.

There is little you can do to speed it up:

increase work_mem in the hope to get a hash join (if you don't already have that)
get faster storage or more RAM to cache data

Get column with text datatype slow query

1 Answers1