Re: [PERFORM] select subquery versus join subquery

Am 05/22/2017 um 09:57 PM schrieb Jeff Janes:
>
> create view view2 as select id,
>   (
>      select md5 from thing_alias where thing_id=id
>         order by priority desc limit 1
>   ) as md5,
>   cutoff from thing;
>
> Cheers,
>
> Jeff

Hi Jeff,

how does something like

CREATE OR REPLACE VIEW public.view3 AS
 SELECT thing.id,
    foo.md5,
    thing.cutoff
   FROM thing,
    LATERAL ( SELECT DISTINCT ON (thing_alias.thing_id)
thing_alias.thing_id,
            thing_alias.md5
           FROM thing_alias
          WHERE thing_alias.thing_id = thing.id
          ORDER BY thing_alias.thing_id, thing_alias.priority DESC) foo

work for you? At least that's always using an index scan here, as
opposed to view1, which (for me) defaults to a SeqScan on thing_alias at
a low cutoff.

*****
Note btw. that both view1 and view2 don't return any md5 values for me,
while view3 does!
*****