It works fine now, on my test server execution time went down from 6.4
seconds to 1.4 seconds and on the production server went down from 3.2
sec to 600ms.
To optimize the query I changed the order of some joins(the joins that
where used to limit rows are at the begining of the query)
I tried s
On May 09, Sterpu Victor modulated:
> I tested it now, EXPLAIN ANALYZE is not showing LEFT JOIN at all if
> I don't select from the joined tables.
> Now is clear why the query is so mutch more efficient when I select
> less data.
>
> Thank you
>
With so many joins, you may want to experiment wit
ctor"
Cc: "Rob Imig" ; "PostgreSQL General"
; "David G. Johnston"
Sent: 9/5/2016 10:04:54 AM
Subject: Re: [GENERAL] Slow query when the select list is big
On 9 May 2016 at 18:46, David G. Johnston
wrote:
On Sunday, May 8, 2016, Sterpu Victor wrote:
Yes but it
.
-- Original Message --
From: "Sterpu Victor"
To: "Sterpu Victor" ; "David Rowley"
Cc: "Rob Imig" ; "PostgreSQL General"
; "David G. Johnston"
Sent: 9/5/2016 11:01:56 AM
Subject: Re[2]: [GENERAL] Slow query when the select li
2016-05-09 11:01 GMT+03:00 Sterpu Victor :
> I went to 2.4 seconds by joining first the tables that produce many rows.
As you're changing your query quite often, it'd be handy, if you could post
both:
- new query version
- it's `EXECUTE (analyze, buffers)` output
If you provide either one or an
DATE(J1031101.validfrom)<= DATE('2016-05-01') ))
ORDER BY J1031101.validfrom DESC
LIMIT 20 OFFSET 0
-- Original Message --
From: "Sterpu Victor"
To: "David Rowley"
Cc: "Rob Imig" ; "PostgreSQL General"
; "David G. Jo
(J1033359.validto IS NULL AND
DATE(J1031101.validfrom)<= DATE('2016-05-01') ))
ORDER BY J1031101.validfrom DESC
LIMIT 20 OFFSET 0
-- Original Message --
From: "David Rowley"
To: "Sterpu Victor"
Cc: "Rob Imig" ; "PostgreSQL General"
On 9 May 2016 at 18:46, David G. Johnston wrote:
> On Sunday, May 8, 2016, Sterpu Victor wrote:
>>
>> Yes but it is very big.
>> I don't understand why the select list is influencing the CPU usage.
>> I was expecting that only the join and where clauses would influence CPU.
>>
>
> PostgreSQL is s
On Sunday, May 8, 2016, Sterpu Victor wrote:
> Yes but it is very big.
> I don't understand why the select list is influencing the CPU usage.
> I was expecting that only the join and where clauses would influence CPU.
>
>
PostgreSQL is smart enough to optimize away stuff that it knows doesn't
imp
On 5/8/2016 11:09 PM, Sterpu Victor wrote:
Yes but it is very big.
I don't understand why the select list is influencing the CPU usage.
I was expecting that only the join and where clauses would influence CPU.
what was the query that generated that really complicated execution
plan? it sure l
Can you share the full query and output of EXPLAIN ? Not much data here
yet.
On Mon, May 9, 2016 at 6:58 AM Sterpu Victor wrote:
> I have a big query that takes about 7 seconds to run(time sending the data
> to the client is not counted).
> Postgres uses 100% of 1 CPU when solving this query. I t
I have a big query that takes about 7 seconds to run(time sending the
data to the client is not counted).
Postgres uses 100% of 1 CPU when solving this query. I tried to run the
query on a HDD and on a SSD with no difference. HDD show about 10% usage
while the query runs.
The query has a big "
12 matches
Mail list logo
