From: Evandro Abreu <evandro.abreu@gmail.com>
To: pgsql-performance@lists.postgresql.org
Date: Sat, 9 Feb 2019 13:45:50 -0300
Message-ID: <CAHaazJdt4naedL-Bhmk2d85NDjXF_QWMOMnsnGcqYLWH3zdiKw@mail.gmail.com> (raw)
I have a report that takes about 20 minutes to generate. It is generated
from 3 tables: according to image.
The report input parameter is a date range. So to generate it I select all
records in Table A and run them
in loop-for. For each record in Table A I make a query Table B join with
Table C where I filter the records through the date field and make the sum
of the value field.
Given this scenario, I would like your help in finding a solution that can
reduce the generation time of this report. System developed in PHP /
Laravel.
PostgreSQL
max_connections = 50
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 83886kB
min_wal_size = 1GB
max_wal_size = 2GB
Linux Server CentOS 7, Single Xeon 4-Core E3-1230 v5 3.4Ghz w / HT, 16GB
RAM.
I've already created indexes in the fields that are involved in the queries.
Database schema
[image: Untitled2.png]Report result
[image: Untitled.png]
--
Atenciosamente,
Evandro Abreu.
Engenheiro de Sistemas at STS Informática Ltda.
Google Talk: evandro.abreu
Twitter: http://twitter.com/abreu_evandro
Skype: evandro_abreu
Facebook: Evandro Abreu <http://www.facebook.com/evandro.abreu.9;
WhatsApp: +55 86 99929-1788
Phone: +55 86 98835-0468
Attachments:Untitled2.png (image/png, 26.2K) downloadview imageUntitled.png (image/png, 155.1K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: evandro.abreu@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re:
In-Reply-To: <CAHaazJdt4naedL-Bhmk2d85NDjXF_QWMOMnsnGcqYLWH3zdiKw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox