PostgreSQL poor lateral join performance

Question

I have tables

dn_table ~ 10_000 rows

|  DN  |
--------
| 1234 |
| 1235 |
| .... |

sr_table ~ 1m rows

|  SR  |
--------
| 2345 |
| 2346 |
| .... |

And I stuck with a lateral join query for them. It has extremely bad performance some queries are running for hours depending on dataset size while selecting with the limit.

select
       *
from (
     select
            "alias1"."DN",
            "alias2"."SR"
     from (
          select "alias1"."DN"
          from "dn_table" as "alias1"
          ) as "alias1" left outer join lateral (
             select *
             from "sr_table" as "alias2"
             where "alias1"."DN" = "alias2"."SR"
             limit 1
         ) as "alias2" on true
     ) as "alias"

I've tried to use correlated subquery for them but it brings me results that I don't expect.

Thanks in advance!

Gordon Linoff · Accepted Answer · 2020-01-30 22:30:42Z

1

A correlated subquery and lateral join should return the same results.

But for your lateral join, you want an index on sr_table(SR). You might also want an order by, but that is a semantic issue, not a performance issue.

If you do add an order by, you'll want to include those columns in the index as well.

answered Jan 30, 2020 at 22:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL poor lateral join performance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related