From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Sterpu Victor <victor(at)caido(dot)ro>, Fırat Güleç <firat(dot)gulec(at)hepsijet(dot)com>, Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Re[4]: Postgresql planning time too high |
Date: | 2019-11-22 18:13:30 |
Message-ID: | CAHOFxGrnr-wu6VMNSjS7BdMUwkkde5OHP99a2iHiTJQyV5wc7w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
As a matter of habit, I put all inner joins that may limit the result set
as the first joins, then the left joins that have where conditions on them.
I am not sure whether the optimizer sees that only those tables are needed
to determine which rows will be in the end result and automatically
prioritizes them as far as joins. With 40+ joins, I would want if this
re-ordering of the declared joins may be significant.
If that doesn't help, then I would put all of those in a sub-query to break
up the problem for the optimizer (OFFSET 0 being an optimization fence,
though if this is an example of "simple" pagination then I assume but am
not sure that OFFSET 20 would also be an optimization fence). Else, put all
that in a CTE with MATERIALIZED keyword when on v12 and without on 9.5
since it did not exist yet and was default behavior then.
With an empty database, there are no statistics so perhaps the optimizer
has too many plans that are very close in expected costs. I'd be curious if
the planning time gets shorter once you have data, assuming
default_statistics_target is left at the standard 100, or is not increased
too hugely.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Luís Roberto Weck | 2019-11-22 18:48:22 | Re: Hash Join over Nested Loop |
Previous Message | Pavel Stehule | 2019-11-22 17:55:34 | Re: Hash Join over Nested Loop |