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

On 05/06/21 21:15, Alexey M Boltenkov wrote:
> 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;
>
I'm sorry, 'archivestatus is not null' is only necessary for index 
without nulls.


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