How to optimise request One sql request is performed 10 times longer with Postgresql than Oracle.
How can we speed up that query with Postgresql?

Hereunder the request:

select t1.c1, t1.c2 from MyTable t1
where t1.c3='string'
and t1.c2=(select max(t2.c2) from MyTable t2 where t2.c4=t1.c4);


The complete query:

select t1.c1, t1.c2 from MyTable t1
left outer join Table t3
on t1.c1=t3.c1
where t1.c3='string'
and t1.c2=(select max(t2.c2) from MyTable t2 where t2.c4=t1.c4);
In Postgres this type of query is much more efficient when done using distinct on () 

    select distinct on (c1) 
         t1.c2, t1.c3
    from MyTable t1
    where MyTable.c4='string'
    order by c1, c3 desc;


Alternatively using standard SQL and a window function: 

select *
from (
  select t1.c2,
         t1.c3,
         row_number() over (partition by c1 order by c3 desc) as rn
  from MyTable t1
  where t1.c2='string'
  and t1.c4 is null
) t
where rn = 1;    




If you can't change the query because this is generated by an obfuscation layer (aka "ORM"), then adding the column c3 to the index idx_multi_c2_c4 and creating an index on (c1,c3) might help.