Table order at FROM clause affects performance?

From: Eduard Català <eduard(dot)catala(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Table order at FROM clause affects performance?
Date: 2018-04-12 14:35:09
Message-ID: CAL54xNYE38bzJGKWbhAe3g_36yc8HNLhxwPJRqs85qdKGAxEmQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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à

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2018-04-12 15:30:28 Re: Table order at FROM clause affects performance?
Previous Message Mark Kirkwood 2018-04-12 05:11:08 Re: Latest advice on SSD?