Same plans different performance?

Поиск
Список
Период
Сортировка
От Elias Panagiotidis
Тема Same plans different performance?
Дата
Msg-id 047501d3a19d$0b364600$21a2d200$@yahoo.gr
обсуждение исходный текст
Список pgsql-performance

Hi community,

 

I successfully use PG for a while but I am new to the community.

 

I have recently written a number of functions that call each other (one of them is recursive). I attach the code of the top-level (plpgsql) functions in the file sql.sql along with the structure of the main table that is used in their queries. In subsequent runs of the following query (with exactly the same parameters) all the results are the expected ones:

 

SELECT dt.c_create_tree(1::smallint, 13, 1::smallint, 110::smallint, ARRAY[1,2]::smallint[], false, ARRAY[22,8,26,1]::smallint[], true, 100, 4, 0.05);

 

However, before I start the optimizing process (many parts of the code are subject to optimization) I noticed that the performance significantly differs (from 45’’ to 7.5’) per run and I can’t understand what is the trigger that enforces this behavior since the plans are always the same (but not the Heap Blocks and the buffers). I noticed that when I restart the PG’s service sometimes (but not always) the first 1 – 5 runs are a lot faster, while, once a run lasts long, all subsequent runs last long too. Also, I noticed that applying ANALYSE of even full VACUM to the main table (pd.d_sample) does not significantly improve the performance if it is already low.

 

The main table on which the queries run is the pd.d_sample that contains around 1.5m rows and the run of the above query updates about 120k of them (the same every time it runs).

 

The two attached logs correspond to excerpts of the EXPLAIN logs of two subsequent runs of the above query the one right after the other. They are pruned because of their size and do not give the total picture, but they cover at least one full iteration and one can see the differences in the Heap Blocks and the buffers from the first few simple queries.

 

My machine has a Core-i7 processor and runs Windows 10. The PG’s version is 9.6.3 64bit.

 

I’d appreciate any help to understand the source of the problem and any potential solution.

 

Thanks in advance,

Elias

Вложения

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

Предыдущее
От: Vitaliy Garnashevich
Дата:
Сообщение: Re: effective_io_concurrency on EBS/gp2
Следующее
От: Robert Klemme
Дата:
Сообщение: Re: OT: Performance of VM