From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gopisetty, Ramesh <rameshg2@illinois.edu>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Performance Issue (Not using Index when joining two tables).
Date: Mon, 14 Sep 2020 13:40:11 -0400
Message-ID: <1124922.1600105211@sss.pgh.pa.us> (raw)
In-Reply-To: <BL0PR11MB31535607A1D9AAB6015D67CBEE230@BL0PR11MB3153.namprd11.prod.outlook.com>
References: <BL0PR11MB3153E3C88990EB0A7976688CEE220@BL0PR11MB3153.namprd11.prod.outlook.com>
<20200913164745.dfnoeiz54wgmgckn@development>
<850901.1600016827@sss.pgh.pa.us>
<CAHOFxGqVg9S3dZS4GHzqefc36FschzDVLsOG_jRgm4MwjfDxVw@mail.gmail.com>
<BL0PR11MB31535607A1D9AAB6015D67CBEE230@BL0PR11MB3153.namprd11.prod.outlook.com>
"Gopisetty, Ramesh" <rameshg2@illinois.edu> writes:
> Thanks for looking into the problem/issue. Let me give more details about the functions... Yes, we are using row level security.
Hm. If those expensive filter functions are being injected by RLS on the
target tables (rather than by something like an intermediate view), then
the planner is constrained to ensure that they execute before any query
conditions that it doesn't know to be "leakproof". So unless your join
operator is leakproof, the shape of plan that you're hoping for will not
be allowed. Since you haven't mentioned anything about data types, it's
hard to know whether that's the issue. (The hash condition seems to be
texteq, which is leakproof, but there are also casts involved which
might not be.)
The two queries you provided explain plans for are not the same, so
comparing their plans is a fairly pointless activity. *Of course*
the query runs faster when you restrict it to fetch fewer rows. The
original query has no restriction clause that corresponds to the
clauses being used for index conditions in the second query, so it's
hardly a surprise that you do not get that plan, RLS or no RLS.
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, rameshg2@illinois.edu, mlewis@entrata.com, tomas.vondra@2ndquadrant.com, pgsql-performance@lists.postgresql.org
Subject: Re: Performance Issue (Not using Index when joining two tables).
In-Reply-To: <1124922.1600105211@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