Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kLAkQ-0003IK-6L for pgsql-bugs@arkaria.postgresql.org; Wed, 23 Sep 2020 19:48:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kLAkO-0006LO-NW for pgsql-bugs@arkaria.postgresql.org; Wed, 23 Sep 2020 19:48:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kLAkO-0006In-HS for pgsql-bugs@lists.postgresql.org; Wed, 23 Sep 2020 19:48:12 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kLAkI-0001MI-71 for pgsql-bugs@lists.postgresql.org; Wed, 23 Sep 2020 19:48:12 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 08NJm3Zr1505477; Wed, 23 Sep 2020 15:48:03 -0400 From: Tom Lane To: Peter Geoghegan cc: XINYULIU@umich.edu, PostgreSQL mailing lists Subject: Re: BUG #16624: Query Optimizer - Performance bug related to predicate simplification In-reply-to: References: <16624-1375434d97659203@postgresql.org> <544018.1600464150@sss.pgh.pa.us> Comments: In-reply-to Peter Geoghegan message dated "Wed, 23 Sep 2020 12:22:52 -0700" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1505475.1600890483.1@sss.pgh.pa.us> Date: Wed, 23 Sep 2020 15:48:03 -0400 Message-ID: <1505476.1600890483@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Peter Geoghegan writes: > Evidently nobody even tries to do this stuff. Yeah. But it would be particularly hard to do in Postgres, because of our resolutely extensible approach to data types and operators/functions. We'd have to devise some general-purpose APIs whereby a specific data type or function could decide whether a given transformation is valid or safe. It's not even very clear where would be the right place to lodge the knowledge. If you were really intent on making this happen, you could imagine for instance attaching a SupportRequestSimplify hook to "int4eq", which could transform "x - c1 = c2" into "x = c1 + c2" after verifying that the constant values wouldn't produce overflow. But this idea doesn't scale very well, since you'd really be talking about looking for a whole bunch of different possible rearrangements not just one. And it's unclear how you could avoid reimplementing a bunch of that logic for each different equality operator. On top of that, you would have little visibility into whether this was worth the cycles, since such code would be unlikely to know whether "x" is an indexable value or whether the expression occurs in a place where it could be used in an indexqual anyway. regards, tom lane