Search code examples
postgresqlpostgresql-12

Get column with text datatype slow query

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?

Solution

  • 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