select distinct runs slow on pg 10.6

yash mehta <[email protected]>
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <CAMgY1WkxX43j47Na4GMHDsOrJQL4M30jw-_Q3qwAWGh8b-MAhQ@mail.gmail.com>
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.

*Original condition: time taken 1min*

Sort Method: external merge  Disk: 90656kB



*After removing distinct from query: time taken 2sec*

Sort Method: top-N heapsort  Memory: 201kB



*After increasing work_mem to 180MB; it takes 20sec*

Sort Method: quicksort  Memory: 172409kB



SELECT * FROM pg_stat_statements ORDER BY total_time DESC limit 1;

-[ RECORD 1
]-------+-----------------------------------------------------------------------------------------------------------------------------------------

userid              | 174862

dbid                | 174861

queryid             | 1469376470

query               | <query is too long. It selects around 300 columns>

calls               | 1

total_time          | 59469.972661

min_time            | 59469.972661

max_time            | 59469.972661

mean_time           | 59469.972661

stddev_time         | 0

rows                | 25

shared_blks_hit     | 27436

shared_blks_read    | 2542

shared_blks_dirtied | 0

shared_blks_written | 0

local_blks_hit      | 0

local_blks_read     | 0

local_blks_dirtied  | 0

local_blks_written  | 0

temp_blks_read      | 257

temp_blks_written   | 11333

blk_read_time       | 0

blk_write_time      | 0
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.