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
