From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Thomas Mayer <thomas.mayer@student.kit.edu>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: window function induces full table scan
Date: Thu, 02 Jan 2014 18:55:55 -0500
Message-ID: <20167.1388706955@sss.pgh.pa.us> (raw)
In-Reply-To: <18380.1388702592@sss.pgh.pa.us>
References: <52C5DAD1.20109@student.kit.edu>
<17191.1388699570@sss.pgh.pa.us>
<CAMkU=1zcZH+7_kzAS_jWR-soz7YWUeu6nYeTtAMhiDt6BQKTpw@mail.gmail.com>
<18380.1388702592@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
I wrote:
> If the restriction clause must give the same answer for any two rows of
> the same partition, then yeah, we could in principle push it down without
> knowing anything about the specific window function. It'd be a less than
> trivial test to make, I think.
On reflection, really this concern is isomorphic to whether or not it is
safe to push quals down into a SELECT DISTINCT. In principle, we should
only do that for quals that cannot distinguish values that are seen as
equal by the equality operator used for DISTINCT. For instance, the
float8 equality operator treats IEEE minus zero and plus zero as "equal",
but it's not hard to contrive a WHERE clause that can tell the difference.
Pushing such a clause down into a SELECT DISTINCT can change the results;
but we do it anyway, and have done so since the nineties, and I don't
recall hearing complaints about this.
If we wanted to be really principled about it, I think we'd have to
restrict pushdown to quals that tested subquery outputs with operators
that are members of the relevant equality operator's btree opclass.
Which would cause a lot of howls of anguish, while making things better
for a set of users that seems to be about empty.
So maybe it'd be all right to push down quals that only reference subquery
outputs that are listed in the PARTITION clauses of all window functions
in the subquery. I think that'd be a reasonably straightforward extension
of the existing tests in allpaths.c.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, jeff.janes@gmail.com, thomas.mayer@student.kit.edu
Subject: Re: window function induces full table scan
In-Reply-To: <20167.1388706955@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