From: Justin Pryzby <pryzby@telsasoft.com>
To: Evandro Abreu <evandro.abreu@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: slow to run query 5000 times
Date: Sat, 9 Feb 2019 11:16:33 -0600
Message-ID: <20190209171633.GO31721@telsasoft.com> (raw)
In-Reply-To: <CAHaazJdt4naedL-Bhmk2d85NDjXF_QWMOMnsnGcqYLWH3zdiKw@mail.gmail.com>
References: <CAHaazJdt4naedL-Bhmk2d85NDjXF_QWMOMnsnGcqYLWH3zdiKw@mail.gmail.com>
Hi,
Please don't send images to the list, you can send a link to one of the image
host websites if you need to describe something graphical.
But here, you could just send the queries and \d for the tables.
On Sat, Feb 09, 2019 at 01:45:50PM -0300, Evandro Abreu wrote:
> 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.
So you're running query 5000 times ?
Do you really need a for loop ? Could you just join the 3 tables together and GROUP BY a.id ?
Please send "explain analyze" for the queries, or a link to the output on
depesz site.
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Information_You_Need_To_Include
Also, are they all taking about the same amount of time ?
Justin
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: pryzby@telsasoft.com, evandro.abreu@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: slow to run query 5000 times
In-Reply-To: <20190209171633.GO31721@telsasoft.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