pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTable order at FROM clause affects performance?
3+ messages / 2 participants
[nested] [flat]
* Table order at FROM clause affects performance?
@ 2018-04-12 14:35 Eduard Català <eduard.catala@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Eduard Català @ 2018-04-12 14:35 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
I have a strange performance issue, i think that is not possible:
Given this statement:
SELECT *several_fields* FROM A, B, C WHERE *conditions*
A, B are tables with several LEFT JOINS but they act as one subquery.
If I execute the select above:
SELECT *several_fields* FROM A, B, C WHERE *conditions*
*Time: 30 secs*
*Cost: 1M*
If I execute the same select (same parameters) but swapping A and B in the
from clause:
SELECT *several_fields* FROM B, A, C WHERE *conditions*
*Time: 19ms*
*Cost: 10k*
The plan changes dramatically: I can't see why the order of FROM clause
impacts directly on the query cost and plan. If this is possible, where i
can read about it? I need to know how the order of FROM clause modifies the
query plan.
Thanks in advance. This is my first post.
Eduard Català
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table order at FROM clause affects performance?
@ 2018-04-12 15:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Eduard Català <eduard.catala@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2018-04-12 15:30 UTC (permalink / raw)
To: Eduard Català <eduard.catala@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
=?UTF-8?Q?Eduard_Catal=C3=A0?= <eduard.catala@gmail.com> writes:
> Given this statement:
> SELECT *several_fields* FROM A, B, C WHERE *conditions*
> A, B are tables with several LEFT JOINS but they act as one subquery.
You really can't expect useful help if you are going to pose questions
that abstract. You have removed details that count, and made assumptions
that don't necessarily hold (e.g., what does "act as one subquery" mean?)
Probably the most likely bet, on this limited information, is that there
are enough base tables hidden inside your query that you're running into
join_collapse_limit and/or from_collapse_limit, resulting in the planner
failing to investigate the best available plan in one case. Raising those
limits would help, if so. But it could easily be something else.
There's some suggestions here about how to ask useful questions:
https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table order at FROM clause affects performance?
@ 2018-04-12 16:25 Eduard Català <eduard.catala@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Eduard Català @ 2018-04-12 16:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
Yes... i can't exepct useful help with my poor explanation
but your aproach is the right answer!
We were limited with from_collapse_limit.
Cost now is: 112
Many many thanks.
On Thu, Apr 12, 2018 at 5:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?UTF-8?Q?Eduard_Catal=C3=A0?= <eduard.catala@gmail.com> writes:
> > Given this statement:
>
> > SELECT *several_fields* FROM A, B, C WHERE *conditions*
> > A, B are tables with several LEFT JOINS but they act as one subquery.
>
> You really can't expect useful help if you are going to pose questions
> that abstract. You have removed details that count, and made assumptions
> that don't necessarily hold (e.g., what does "act as one subquery" mean?)
>
> Probably the most likely bet, on this limited information, is that there
> are enough base tables hidden inside your query that you're running into
> join_collapse_limit and/or from_collapse_limit, resulting in the planner
> failing to investigate the best available plan in one case. Raising those
> limits would help, if so. But it could easily be something else.
>
> There's some suggestions here about how to ask useful questions:
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-04-12 16:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-12 14:35 Table order at FROM clause affects performance? Eduard Català <eduard.catala@gmail.com>
2018-04-12 15:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-04-12 16:25 ` Eduard Català <eduard.catala@gmail.com>
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