Re: select distinct runs slow on pg 10.6

There are few things to consider:
- you don't need to use distinct on all columns (and therefore sort all
columns)
- you should try to sort in memory, better than on-disk
- it seems that the planner doesn't predict the good number of rows

Regards,
Florian

On Mon, Sep 9, 2019 at 12:46 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> On Mon, Sep 09, 2019 at 02:00:01PM +0530, yash mehta wrote:
> > We have a query that takes 1min to execute in postgres 10.6 and the same
> > executes in 4 sec in Oracle database. The query is doing 'select
> distinct'.
> > If I add a 'group by' clause, performance in postgres improves
> > significantly and fetches results in 2 sec (better than oracle). But
> > unfortunately, we cannot modify the query. Could you please suggest a way
> > to improve performance in Postgres without modifying the query.
>
> Not sure it helps, but I remember this:
>
> https://www.postgresql.org/message-id/CAKJS1f9q0j3BgMUsDbtf9%3DecfVLnqvkYB44MXj0gpVuamcN8Xw%40mail.gmail.com
>
>
>