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

On Thu, May 6, 2021 at 10:38 AM Semen Yefimenko <semen.yefimenko@gmail.com>
wrote:

> Hi there,
>
> I've recently been involved in migrating our old system to SQL Server and
> then PostgreSQL. Everything has been working fine so far but now after
> executing our tests on Postgres, we saw a very slow running query on a
> large table in our database.
> I have tried asking on other platforms but no one has been able to give me
> a satisfying answer.
> ...
>
> SELECT column1,..., column54  where ((entrytype = 4000 or entrytype = 4001
> or entrytype = 4002) and (archivestatus <= 1)) order by timestampcol desc;
>
>
>
I know several people have suggested using `IN` to replace the or
statements, that would be my first go-to also.  Another approach I have
found helpful is to keep in mind whenever you have an  `OR`  in a where
clause it can be replaced with a `UNION ALL`.  Usually the `UNION ALL` is
faster.

I recommend avoiding `OR` in where clauses as much as possible.  -
Sometimes it can't be helped, especially if you need an exclusive or, but
most of the time there is another way that is usually better.

Another thought is "archivestatus" really a boolean or does it have
multiple states?  If it is actually a boolean, then can you change the
column data type?