select distinct runs slow on pg 10.6

Поиск
Список
Период
Сортировка
От yash mehta
Тема select distinct runs slow on pg 10.6
Дата
Msg-id CAMgY1WkxX43j47Na4GMHDsOrJQL4M30jw-_Q3qwAWGh8b-MAhQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: select distinct runs slow on pg 10.6
Re: select distinct runs slow on pg 10.6
Список pgsql-performance
Дерево обсуждения
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

В списке pgsql-performance по дате отправления: