pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: James Coleman <jtc331@gmail.com>
Cc: Jaime Casanova <jaime.casanova@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: enable_incremental_sort changes query behavior
Date: Sat, 3 Oct 2020 00:28:57 +0200
Message-ID: <20201002222857.t3sijcldxcdt2d6d@development> (raw)
In-Reply-To: <CAAaqYe-PyV1Ap=tF8C1am1Oqw0MZDPg4JZiaR_ZmTNGb8tG45w@mail.gmail.com>
References: <20201001220822.rwwxajwzobljgqwz@development>
	<CAAaqYe-wssm0qqc0sDm7+5mNTAUYKfXx9pnGcD7vCS3g49Gmmw@mail.gmail.com>
	<CAAaqYe83Ec57fqJiA0fkEGQ-PUk_VwiR2nHbg=LHPwiG5jtXJQ@mail.gmail.com>
	<20201002143207.qt7re34mhsfmfi6f@development>
	<CAAaqYe-bMpL=adD764Cqr9AomvOeH4MeYpppBVKuwd+MtqDeOw@mail.gmail.com>
	<CAAaqYe9g49hwumqz3NrNwtTnhnoEQ8Bqh5ZPgPDusMD8Dirm6Q@mail.gmail.com>
	<20201002182508.cbux56rfoak2jzdf@development>
	<CAAaqYe_5OG8tOfj+2OxhyOqBggSXZLC_WoWqbZFW7tD0f4+oPg@mail.gmail.com>
	<20201002205620.sbmfqdmkprjrlpgz@development>
	<CAAaqYe-PyV1Ap=tF8C1am1Oqw0MZDPg4JZiaR_ZmTNGb8tG45w@mail.gmail.com>

On Fri, Oct 02, 2020 at 05:45:52PM -0400, James Coleman wrote:
>On Fri, Oct 2, 2020 at 4:56 PM Tomas Vondra
><tomas.vondra@2ndquadrant.com> wrote:
>>
>> ...
>>
>> More importanly, it does not actually fix the issue - it does fix that
>> particular query, but just replacing the DISTINCT with either ORDER BY
>> or GROUP BY make it fail again :-(
>>
>> Attached is a simple script I used, demonstrating these issues for the
>> three cases that expect to have ressortgroupref != 0 (per the comment
>> before TargetEntry in plannodes.h).
>
>So does checking for volatile expressions (if you happened to test
>that) solve all the cases? If you haven't tested that yet, I can try
>to do that this evening.
>

Yes, it does fix all the three queries in the SQL script.

The question however is whether this is the root issue, and whether it's
the right way to fix it. For example - volatility is not the only reason
that may block qual pushdown. If you look at qual_is_pushdown_safe, it
also blocks pushdown of leaky functions in security_barrier views. So I
wonder if that could cause failures too, somehow. But I haven't managed
to create such example.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





view thread (61+ messages)  latest in thread

Message-ID: <20201002222857.t3sijcldxcdt2d6d@development>
Permalink:  ../20201002222857.t3sijcldxcdt2d6d@development/
Also on:    postgresql.org/message-id/20201002222857.t3sijcldxcdt2d6d@development

 · 

reply
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-hackers@postgresql.org
  Cc: tomas.vondra@2ndquadrant.com, jtc331@gmail.com, jaime.casanova@2ndquadrant.com
  Subject: Re: enable_incremental_sort changes query behavior
  In-Reply-To: <20201002222857.t3sijcldxcdt2d6d@development>

* 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