Re: Very slow Query compared to Oracle / SQL - Server

On 05/06/21 19:11, luis.roberto@siscobra.com.br wrote:
> ----- Mensagem original -----
>> De: "Semen Yefimenko" <semen.yefimenko@gmail.com>
>> Para: "pgsql-performance" <pgsql-performance@lists.postgresql.org>
>> Enviadas: Quinta-feira, 6 de maio de 2021 11:38:39
>> Assunto: Very slow Query compared to Oracle / SQL - Server
>
>> SELECT column1,..., column54 where ((entrytype = 4000 or entrytype = 4001 or
>> entrytype = 4002) and (archivestatus <= 1)) order by timestampcol desc;
>   
>
> The first thing I would try is rewriting the query to:
>
> SELECT column1,..., column54
>    FROM logtable
>   WHERE (entrytype in (4000,4001,4002))
>     AND (archivestatus <= 1))
>   ORDER BY timestampcol DESC;
>
> Check if that makes a difference...
>
> Luis R. Weck
>
>
>
The IN statement will probable result in just recheck condition change 
to entrytype = any('{a,b,c}'::int[]). Looks like dispersion of 
archivestatus is not enough to use index idx_arcstatus.

Please try to create partial index with condition like (archivestatus <= 
1) and rewrite select to use (archivestatus is not null and 
archivestatus <= 1).

CREATE INDEX idx_arcstatus_le1 ON schema.logtable ( archivestatus ) 
where (archivestatus <= 1) TABLESPACE tablespace;