Plan to support predicate push-down into subqueries with aggregates?

From: Adam Brusselback <adambrusselback(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Plan to support predicate push-down into subqueries with aggregates?
Date: 2016-03-08 21:38:56
Message-ID: CAMjNa7cYSi8aXe=thZZvuXpwcLUnQHORPNb0YETgUn5z5ZVi1Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all.
I was wondering if there were any plans to support predicate push-down
optimization for subqueries (and views) with aggregates?

I was recently bit by this, as I had incorrectly assumed that this was an
optimization that was in place, and designed quite a bit around that
assumption, only to get hit with terrible performance when more data got
loaded into the system.

Currently I had to solve the issue by having aggregate tables which store
the data, which is maintained by triggers on IUD. This gets messy quick,
as I have some aggregates which are dependent on 5-6 other tables. I'd
love to be able to just store the logic for calculating aggregates in a
view, and use that at query time instead of having to deal with it like
this.

I have written a test case script that explains the type of queries I am
talking about, and the issues. It's heavily simplified compared to a real
system, but it'll do. Please see attached.

Thanks,
-Adam

Attachment Content-Type Size
subquery_pushdown.txt text/plain 2.4 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2016-03-08 22:17:23 Re: Plan to support predicate push-down into subqueries with aggregates?
Previous Message Andreas Joseph Krogh 2016-03-08 20:06:30 Re: Exclude pg_largeobject form pg_dump