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

[Alexey M Boltenkov <padrebolt@xxxxxxxxx>]

On 05/06/21 21:15, Alexey M Boltenkov wrote:

On 05/06/21 19:11, luis.roberto@xxxxxxxxxxxxxxx wrote:

----- Mensagem original -----

De: "Semen Yefimenko" <semen.yefimenko@xxxxxxxxx>
Para: "pgsql-performance" <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>
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;