Re: select subquery versus join subquery

Am 05/23/2017 um 06:59 PM schrieb Jeff Janes:
> On Tue, May 23, 2017 at 4:03 AM, Gunnar "Nick" Bluth
> <gunnar(dot)bluth(dot)extern(at)elster(dot)de <mailto:gunnar(dot)bluth(dot)extern(at)elster(dot)de>>
> wrote:
8>< -----
>
> Unfortunately that always uses the index scan, even at a high cutoff
> where aggregation on the seq scan and then hash joining is more
> appropriate. So it is very similar to view2, except that it doesn't
> return the rows from "thing" which have zero corresponding rows in
> thing_alias.
>
> *****
> Note btw. that both view1 and view2 don't return any md5 values for me,
> while view3 does!
> *****
>
>
> Because of the way I constructed the data, using the power transform of
> the uniform random distribution, the early rows of the view (if sorted
> by thing_id) are mostly null in the md5 column, so if you only look at
> the first few screen-fulls you might not see any md5. But your view
> does effectively an inner join rather than a left join, so your view
> gets rid of the rows with a NULL md5. Most things don't have aliases;
> of the things that do, most have 1; and some have a several.

D'oh, of course! My bad... shouldn't have looked at the results with
LIMIT :-/

My next best guess would involve a MatView for the aggregates...
--
Gunnar "Nick" Bluth
DBA ELSTER

Tel: +49 911/991-4665
Mobil: +49 172/8853339