Search Postgresql Archives

Re: Slow SELECT

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

 



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.

SELECT a.*
FROM table_1 AS b
CROSS JOIN table_2 AS c
CROSS JOIN table_3 AS d
CROSS JOIN table_4 AS e
CROSS JOIN LATERAL (
    SELECT *
    FROM my_table AS a
    WHERE (a.fld_1, a.fld_2, a.fld_3, a.fld_4) = (b.row_id, c.row_id,
d.row_id, e.row_id)
      AND a.deleted = 0
    ORDER BY a.tran_date DESC
    FETCH FIRST ROW ONLY
) AS a
WHERE a.row_id IS NOT NULL;

You will likely want an index on my_table (fld_1, fld_2, fld_3, fld_4,
tran_date) if your dataset gets bigger.

This query is 100% Standard SQL, so it *should* work on other engines.
That doesn't mean it will, though.
-- 
Vik Fearing





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux