Same plans different performance?

From: "Elias Panagiotidis" <eliaspanagiotidis(at)yahoo(dot)gr>
To: <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Same plans different performance?
Date: 2018-02-09 11:56:38
Message-ID: 047501d3a19d$0b364600$21a2d200$@yahoo.gr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Attachment Content-Type Size
explain_slow.log application/octet-stream 287.4 KB
explain_fast.log application/octet-stream 261.7 KB
sql.sql text/plain 6.1 KB

Browse pgsql-performance by date

  From Date Subject
Next Message Robert Klemme 2018-02-10 11:20:59 Re: OT: Performance of VM
Previous Message Vitaliy Garnashevich 2018-02-08 16:40:12 Re: effective_io_concurrency on EBS/gp2