On Thu, May 6, 2021 at 10:38 AM Semen Yefimenko <semen.yefimenko@xxxxxxxxx> 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?
