Thread: Joining against a view that uses an aggregate - performance issue
https://gist.github.com/joevandyk/070e4728c4c9fe1bf086/raw/8b1ecf4b2d4fd127a22cb19abe948c29d78c2158/gistfile1.txt summarizes the problem.
andres on #postgresql says that making #2 use a faster plan shouldn't be hard, but he doesn't seem #3 happening.
I was surprised about #2 not being faster, andres said "Afaics its this restriction: "1. The qual must not contain any subselects (mainly because I'm not sure it will work correctly: sublinks will already have been transformed into subplans in the qual, but not in the subquery)." in qual_is_pushdown_safe"
Not sure if there's anything to be done here, just thought I'd post in case anyone has any ideas. In an ideal world, I'd be able to write version #3.
Joe
Oops, fixing link.
https://gist.github.com/joevandyk/070e4728c4c9fe1bf086/raw/8b1ecf4b2d4fd127a22cb19abe948c29d78c2158/gistfile1.txt
summarizes the problem.
https://gist.github.com/joevandyk/070e4728c4c9fe1bf086/raw/8b1ecf4b2d4fd127a22cb19abe948c29d78c2158/gistfile1.txt
summarizes the problem.
On Fri, Mar 8, 2013 at 4:17 PM, Joe Van Dyk <joe@tanga.com> wrote:
https://gist.github.com/joevandyk/070e4728c4c9fe1bf086/raw/8b1ecf4b2d4fd127a22cb19abe948c29d78c2158/gistfile1.txt summarizes the problem.andres on #postgresql says that making #2 use a faster plan shouldn't be hard, but he doesn't seem #3 happening.I was surprised about #2 not being faster, andres said "Afaics its this restriction: "1. The qual must not contain any subselects (mainly because I'm not sure it will work correctly: sublinks will already have been transformed into subplans in the qual, but not in the subquery)." in qual_is_pushdown_safe"Not sure if there's anything to be done here, just thought I'd post in case anyone has any ideas. In an ideal world, I'd be able to write version #3.Joe
Here's a much smaller self-contained example of the problem:
Joe
On Fri, Mar 8, 2013 at 4:17 PM, Joe Van Dyk <joe@tanga.com> wrote:
https://gist.github.com/joevandyk/070e4728c4c9fe1bf086/raw/8b1ecf4b2d4fd127a22cb19abe948c29d78c2158/gistfile1.txt summarizes the problem.andres on #postgresql says that making #2 use a faster plan shouldn't be hard, but he doesn't seem #3 happening.I was surprised about #2 not being faster, andres said "Afaics its this restriction: "1. The qual must not contain any subselects (mainly because I'm not sure it will work correctly: sublinks will already have been transformed into subplans in the qual, but not in the subquery)." in qual_is_pushdown_safe"Not sure if there's anything to be done here, just thought I'd post in case anyone has any ideas. In an ideal world, I'd be able to write version #3.Joe