From: | Rick Otten <rottenwindfish(at)gmail(dot)com> |
---|---|
To: | Semen Yefimenko <semen(dot)yefimenko(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Very slow Query compared to Oracle / SQL - Server |
Date: | 2021-05-07 12:50:08 |
Message-ID: | CAMAYy4JJx7UEBTRBWvEKBqXfFzJPpKXswhK9enexhZGEcZ8oqg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, May 6, 2021 at 10:38 AM Semen Yefimenko <semen(dot)yefimenko(at)gmail(dot)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?
From | Date | Subject | |
---|---|---|---|
Next Message | Semen Yefimenko | 2021-05-07 15:57:19 | Re: Very slow Query compared to Oracle / SQL - Server |
Previous Message | Vijaykumar Jain | 2021-05-07 09:55:58 | Re: Very slow Query compared to Oracle / SQL - Server |