From: | Alexey M Boltenkov <padrebolt(at)yandex(dot)ru> |
---|---|
To: | luis(dot)roberto(at)siscobra(dot)com(dot)br, Semen Yefimenko <semen(dot)yefimenko(at)gmail(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Very slow Query compared to Oracle / SQL - Server |
Date: | 2021-05-06 18:15:36 |
Message-ID: | 9a713ea4-f672-412c-70c3-34b82c275b7f@yandex.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 05/06/21 19:11, luis(dot)roberto(at)siscobra(dot)com(dot)br wrote:
> ----- Mensagem original -----
>> De: "Semen Yefimenko" <semen(dot)yefimenko(at)gmail(dot)com>
>> Para: "pgsql-performance" <pgsql-performance(at)lists(dot)postgresql(dot)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;
From | Date | Subject | |
---|---|---|---|
Next Message | Alexey M Boltenkov | 2021-05-06 18:20:28 | Re: Very slow Query compared to Oracle / SQL - Server |
Previous Message | luis.roberto | 2021-05-06 16:11:07 | Re: Very slow Query compared to Oracle / SQL - Server |