Re: Slow SELECT

On Tue, 26 May 2020 at 23:41, Vik Fearing <vik(at)postgresfriends(dot)org> wrote:
>
> On 5/26/20 12:04 PM, David Rowley wrote:
> > Since "my_table" is small, you'd likely be much better doing a manual
> > rewrite of the query to join a subquery containing the required
> > details from "my_table". It looks like you want the row_id from the
> > latest tran_date for each fld_N column. So something like:
> >
> > SELECT a.row_id
> > FROM table_1 b
> > CROSS JOIN table_2 c
> > CROSS JOIN table_3 d
> > CROSS JOIN table_4 e
> > LEFT OUTER JOIN (
> > SELECT fld_1,fld_2,fld_3,fld_4,row_id,tran_date,
> > ROW_NUMBER() OVER (PARTITION BY fld_1,fld_2,fld_3,fld_4 ORDER BY
> > tran_date DESC) row_num
> > FROM my_table
> > WHERE deleted_id = 0
> > ) a ON a.fld_1 = b.row_id AND a.fld_2 = c.row_id AND a.fld_3 =
> > d.row_id AND a.fld_4 = e.row_id AND a.row_num = 1;
> >
> > Should do it. You could also perhaps do something with DISTINCT ON
> > instead of using ROW_NUMBER(). That might be a bit more efficient, but
> > it's unlikely to matter too much since there are only 167 rows in that
> > table.
>
> I would expect a lateral query to be better here.

But that would put it back to executing the subquery 11088 times. I
wrote it in a way to avoid that.

David