From: Tom Lane <tgl@sss.pgh.pa.us>
To: A Guy Named Ryan <aguynamedryan@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Execution time from >1s -> 80m+ when extra columns added in SELECT for sub-query
Date: Mon, 18 May 2020 17:58:48 -0400
Message-ID: <19695.1589839128@sss.pgh.pa.us> (raw)
In-Reply-To: <CAAJz3y2QnyCAJOoSx0P5U9th0cAH1rtQ5nqC_UwqfDZGW9mdKg@mail.gmail.com>
References: <CAAJz3y3cUhHx7y0ffr9i1Cm4V93CwgnNDY7+uV2GzTQLrn5Q-A@mail.gmail.com>
<CAFj8pRCEXjXoxDVXAqNE2qqkEHWM+=ZWHg9qo=soJf0vbK9Y3w@mail.gmail.com>
<CAAJz3y2QnyCAJOoSx0P5U9th0cAH1rtQ5nqC_UwqfDZGW9mdKg@mail.gmail.com>
A Guy Named Ryan <aguynamedryan@gmail.com> writes:
> Why would the planner switch plans so drastically given that all I'm
> doing is including a few extra columns in the subselect, particularly
> when those columns are discarded by the super? parent? subselect
The problem is that the columns you're adding *don't belong to that
table*. Per your schema dump,
jtemp1c37l3b_baseline_windows_with_collections only contains the columns
person_id and uuid. So when you write
SELECT
"uuid" AS "uuid",
CAST(NULL AS float) AS "drug_amount",
CAST(NULL AS text) AS
"drug_amount_units",
CAST(NULL AS bigint) AS
"drug_days_supply",
CAST(NULL AS text) AS "drug_name",
CAST(NULL AS float) AS "drug_quantity",
CAST(NULL AS integer) AS "window_id",
"person_id" AS "person_id",
"criterion_id" AS "criterion_id",
"criterion_table" AS "criterion_table",
"criterion_domain" AS
"criterion_domain",
"start_date" AS "start_date",
"end_date" AS "end_date"
FROM
"jigsaw_temp"."jtemp1c37l3b_baseline_windows_with_collections"
those are the only two columns that are "legitimately" part of that bottom
sub-select, and the others are outer references to
jtemp1c37l3b_baseline_windows_after_inclusion. That's legal per SQL,
but it makes the EXISTS into a correlated sub-select, which is something
we can't turn into a semijoin.
Indeed, the unreferenced columns do get thrown away later, but that
doesn't happen until well past the point where the join restructuring
decisions are made (and there are good reasons for that ordering of
operations).
Basically I'd write this off as "broken SQL code generator". If it
doesn't understand the difference between a local reference and an
outer reference, you shouldn't be letting it near your database.
That sort of fundamental misunderstanding often leads to incorrect
query results, never mind whether the query is fast or not.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, aguynamedryan@gmail.com, pavel.stehule@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Execution time from >1s -> 80m+ when extra columns added in SELECT for sub-query
In-Reply-To: <19695.1589839128@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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