Re: select subquery versus join subquery

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Am 05/23/2017 um 06:59 PM schrieb Jeff Janes:
> On Tue, May 23, 2017 at 4:03 AM, Gunnar "Nick" Bluth
> <gunnar.bluth.extern@xxxxxxxxx <mailto:gunnar.bluth.extern@xxxxxxxxx>>
> 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

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux