Re: slow to run query 5000 times

2019-02-09 Thread Justin Pryzby 
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

Re: slow to run query 5000 times

2019-02-09 Thread Andres Freund 
Hi,

On 2019-02-09 11:16:33 -0600, Justin Pryzby wrote:
> 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.

FWIW, I don't agree with that policy. I plenty of time process email
while withoug internet, so I appreciate self contained email.

Greetings,

Andres Freund

Re: slow to run query 5000 times

2019-02-09 Thread Ricardo Martin Gomez 
Hi,
Do you have an index in the date field?

Obtener Outlook para Android


From: Andres Freund 
Sent: Saturday, February 9, 2019 5:23:14 PM
To: Justin Pryzby
Cc: Evandro Abreu; [email protected]
Subject: Re: slow to run query 5000 times

Hi,

On 2019-02-09 11:16:33 -0600, Justin Pryzby wrote:
> 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.

FWIW, I don't agree with that policy. I plenty of time process email
while withoug internet, so I appreciate self contained email.

Greetings,

Andres Freund