I have these two queries in Postgres. One is:

SELECT _id, created_at
FROM pedidos
WHERE _id = '123abc;

Works fine, returns:

{
    "_id": "123abc",
    "created_at": "Sun Jun 08 2025 10:28:23 GMT-0300 (Brasilia Standard Time)",
  }

The other:

SELECT i.id, i.objeto
FROM pedidos, jsonb_array_elements(objeto) WITH ORDINALITY i(objeto, id)
WHERE _id = '123abc';

Also works fine, returns:

[
        {
            "id": "1",
            "objeto": {
                "peso": 76,
                "valor": 1290,
                "altura": 1001,
                "largura": 211,
                "comprimento": 298
            }
        },
        {
            "id": "2",
            "objeto": {
                "peso": 77,
                "valor": 1291,
                "altura": 1002,
                "largura": 212,
                "comprimento": 299
            }
        }
  ]

I can run both separately and merge to obtain:

{
    "_id": "123abc",
    "created_at": "Sun Jun 08 2025 10:28:23 GMT-0300 (Brasilia Standard Time)",
    "objetos": [
        {
            "id": "1",
            "objeto": {
                "peso": 76,
                "valor": 1290,
                "altura": 1001,
                "largura": 211,
                "comprimento": 298
            }
        },
        {
            "id": "2",
            "objeto": {
                "peso": 77,
                "valor": 1291,
                "altura": 1002,
                "largura": 212,
                "comprimento": 299
            }
        }
    ]
  }

Is it possible to merge the two queries into one? I tried INNER JOIN but that returned: > i._id not found

# Setup

CREATE TABLE persons
(
  person_id int not null,
  name TEXT 
);

INSERT INTO persons VALUES
(1, 'Adam'),
(2, 'Paul'),
(3, 'Tye'),
(4, 'Sarah');

CREATE TABLE json_to_parse
(
  person_id int not null,
  block json
);

INSERT INTO json_to_parse VALUES
  (1, '{"size": "small", "love": "x"}'),
  (2, '{"size": "medium", "love": "xx"}'),
  (3, '{"size": "big", "love": "xxx"}');

# The Error This runs without issue

SELECT
  *
FROM
  json_to_parse
CROSS JOIN LATERAL
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id;

but this does not

SELECT
  *
FROM
  json_to_parse,
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id;

and I get the error "invalid reference to FROM-clause entry for table "json_to_parse"" Why does this second query error? [The docs](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL) make it quite clear that LATERAL is optional for table-valued functions > Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function's arguments can contain references to columns provided by preceding FROM items in any case. [dbfiddle](https://dbfiddle.uk/An86bWru)

I have a costly function and it only provides relevant data when certain conditions are met. That data is inserted into the result through a LEFT JOIN LATERAL as shown below. As it works now, the function is called for every row. Is there a way to rewrite it so that it is only called for rows where t1.type is equal to 5? The below still calls the function for every row, although I have a condition set. SELECT t1.name, t1.type, t2.col1, t2.col2 FROM table1 t1 LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.type = 5; Test case: CREATE TABLE table1 ( id SERIAL PRIMARY KEY, name VARCHAR(100), type INTEGER, col1 INTEGER, col2 INTEGER ); -- Sample data for table1 INSERT INTO table1 (name, type, col1, col2) VALUES ('Row 1', 5, 10, 20), ('Row 2', 7, 15, 25), ('Row 3', 10, 30, 40); CREATE OR REPLACE FUNCTION function_name(col1_arg INTEGER, col2_arg INTEGER) RETURNS TABLE (col1 INTEGER, col2 INTEGER) AS $$ BEGIN RAISE LOG 'Function called'; RETURN QUERY SELECT col1_arg * 2 AS col1, col2_arg * 3 AS col2; END; $$ LANGUAGE plpgsql; -- Test Query SELECT t1.name, t1.type, t2.col1, t2.col2 FROM table1 t1 LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.type = 5;

**tl;dr: When to use array_agg over a lateral join with array subquery?** Context:

create table t_zipcode ( zipcode text primary key, location geography );
create table t_user ( id text primary key, zipcode text references t_zipcode);

I want to design a query that gets all 'nearby' zipcodes to each user, say in a range of 20 km. My first attempt was like this:

select u.id, z.zipcode, z.location, array_agg(zz.zipcode order by zz.zipcode) as nearby_zipcodes
from t_user u
         join t_zipcode z on u.zipcode = z.zipcode
         left join t_zipcode zz on st_dwithin(z.location, zz.location, 20000)
group by u.id, z.zipcode, z.location
order by u.id;

which returns results like this:

+------------------------------------+-------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|id                                  |zipcode|location                                          |nearby_zipcodes                                                                                                                                                                                                                                                                                                                            |
+------------------------------------+-------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|0233684d-d2d8-4fdc-863f-08c6aac79d92|81001  |0101000020E6100000C4245CC823225AC025E7C41EDA254340|{81001,81003,81006,81008,81025}                                                                                                                                                                                                                                                                                                            |
|0e59fd58-00a5-442e-a21e-16ab1f4d2f65|85226  |0101000020E6100000506EDBF7A8005CC016A243E048A04040|{85034,85040,85041,85042,85044,85045,85048,85121,85202,85224,85225,85226,85248,85282,85283,85284,85286,85339}                                                                                                                                                                                                                              |
|13f26eca-bda0-4b8f-bcff-c95cceee3421|81001  |0101000020E6100000C4245CC823225AC025E7C41EDA254340|{81001,81003,81006,81008,81025}                                                                                                                                                                                                                                                                                                            |
|2d69e6bd-e0f0-40aa-ba3a-0e157fff46ff|81001  |0101000020E6100000C4245CC823225AC025E7C41EDA254340|{81001,81003,81006,81008,81025}                                                                                                                                                                                                                                                                                                            |
|4bd8e913-7a75-4028-8254-faebf043f629|81001  |0101000020E6100000C4245CC823225AC025E7C41EDA254340|{81001,81003,81006,81008,81025}                                                                                                                                                                                                                                                                                                            |
|627897e0-595c-4d21-b1b1-8dd43f21dee7|85033  |0101000020E6100000DAC69FA86C0D5CC08E78B29B19BF4040|{85003,85004,85006,85007,85009,85012,85013,85014,85015,85016,85017,85019,85020,85021,85023,85029,85031,85033,85034,85035,85037,85040,85041,85043,85051,85053,85064,85301,85302,85303,85304,85305,85306,85307,85308,85309,85323,85335,85340,85345,85351,85353,85363,85378,85379,85381,85382,85392,85395}                                    |
|6353efe7-b0e0-42a2-b5fd-f38b7c8e27ad|81001  |0101000020E6100000C4245CC823225AC025E7C41EDA254340|{81001,81003,81006,81008,81025}                                                                                                                                                                                                                                                                                                            |
|6efad24b-10a7-4ac7-b91a-8842bca70c06|81001  |0101000020E6100000C4245CC823225AC025E7C41EDA254340|{81001,81003,81006,81008,81025}                                                                                                                                                                                                                                                                                                            |
|7f1fe1d1-e2cd-44b9-85f0-b3b888823a20|85305  |0101000020E6100000BF7D1D3867105CC0D2C6116BF1C34040|{85003,85004,85007,85009,85012,85013,85014,85015,85017,85019,85020,85021,85023,85029,85031,85033,85035,85037,85043,85051,85053,85080,85301,85302,85303,85304,85305,85306,85307,85308,85309,85323,85335,85340,85345,85351,85353,85355,85363,85373,85374,85375,85378,85379,85381,85382,85388,85392,85395}                                    |
|9c238c4a-712b-4b10-a91d-6c3548ae59bc|86001  |0101000020E6100000054F2157EAE85BC0D6AA5D13D2A44140|{86001,86011,86015}                                                                                                                                                                                                                                                                                                                        |
|ace88100-9ecd-4931-a617-b8d8b091470e|85007  |0101000020E6100000CB30EE06D1055CC0AA9D616A4BB94040|{85003,85004,85006,85007,85008,85009,85012,85013,85014,85015,85016,85017,85018,85019,85020,85021,85028,85029,85031,85033,85034,85035,85037,85040,85041,85042,85043,85044,85045,85048,85051,85064,85251,85253,85257,85281,85282,85283,85284,85301,85302,85303,85304,85305,85353}                                                            |
|bf96321b-be2e-4ef9-801e-9ee82ce693f4|81001  |0101000020E6100000C4245CC823225AC025E7C41EDA254340|{81001,81003,81006,81008,81025}                                                                                                                                                                                                                                                                                                            |
|d787537e-a3fa-4267-b313-71b2c07027fe|81001  |0101000020E6100000C4245CC823225AC025E7C41EDA254340|{81001,81003,81006,81008,81025}                                                                                                                                                                                                                                                                                                            |
|ea8d074d-adb1-4c45-bd02-b6b57f5a6604|41091  |0101000020E61000005B0A48FB1F2F55C063D009A183744340|{41005,41018,41030,41042,41051,41080,41091,41092,41094,41095,47020,47038,47040}                                                                                                                                                                                                                                                            |
|f925949e-6a5e-4e2c-9591-60f05f42ccb1|81001  |0101000020E6100000C4245CC823225AC025E7C41EDA254340|{81001,81003,81006,81008,81025}                                                                                                                                                                                                                                                                                                            |
|fc47120d-d3da-4847-8134-0eca87708467|98102  |0101000020E61000001C78B5DC99945EC09A7CB3CD8DD14740|{98004,98005,98006,98007,98008,98011,98020,98021,98028,98033,98034,98036,98039,98040,98043,98052,98056,98057,98072,98101,98102,98103,98104,98105,98106,98107,98108,98109,98110,98112,98115,98116,98117,98118,98119,98121,98122,98125,98126,98133,98134,98136,98144,98146,98154,98155,98164,98168,98174,98177,98178,98195,98199,98342,98353}|
+------------------------------------+-------+--------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This is the analyzed plan with just 16 users AND using an index on the zipcode location column:

+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                     |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|GroupAggregate  (cost=85365.87..86252.54 rows=35467 width=112) (actual time=1.932..2.474 rows=16 loops=1)                                                      |
|  Output: u.id, z.zipcode, z.location, array_agg(zz.zipcode ORDER BY zz.zipcode)                                                                               |
|  Group Key: u.id, z.zipcode, z.location                                                                                                                       |
|  ->  Sort  (cost=85365.87..85454.53 rows=35467 width=112) (actual time=1.887..1.913 rows=277 loops=1)                                                         |
|        Output: u.id, z.zipcode, z.location, zz.zipcode                                                                                                        |
|        Sort Key: u.id, z.zipcode, z.location                                                                                                                  |
|        Sort Method: quicksort  Memory: 53kB                                                                                                                   |
|        ->  Nested Loop Left Join  (cost=0.69..81507.59 rows=35467 width=112) (actual time=0.197..1.641 rows=277 loops=1)                                      |
|              Output: u.id, z.zipcode, z.location, zz.zipcode                                                                                                  |
|              ->  Nested Loop  (cost=0.29..719.73 rows=1070 width=80) (actual time=0.043..0.136 rows=16 loops=1)                                               |
|                    Output: u.id, z.zipcode, z.location                                                                                                        |
|                    Inner Unique: true                                                                                                                         |
|                    ->  Seq Scan on pg_temp.t_user u  (cost=0.00..20.70 rows=1070 width=48) (actual time=0.017..0.021 rows=16 loops=1)                         |
|                          Output: u.id, u.zipcode                                                                                                              |
|                    ->  Index Scan using test_idx_user_zip on pg_temp.t_zipcode z  (cost=0.29..0.65 rows=1 width=64) (actual time=0.006..0.006 rows=1 loops=16)|
|                          Output: z.zipcode, z.location                                                                                                        |
|                          Index Cond: (z.zipcode = u.zipcode)                                                                                                  |
|              ->  Index Scan using test_idx_zip_geo on pg_temp.t_zipcode zz  (cost=0.40..75.47 rows=3 width=64) (actual time=0.040..0.089 rows=17 loops=16)    |
|                    Output: zz.zipcode, zz.location                                                                                                            |
|                    Index Cond: (zz.location && _st_expand(z.location, '20000'::double precision))                                                             |
|                    Filter: st_dwithin(z.location, zz.location, '20000'::double precision, true)                                                               |
|                    Rows Removed by Filter: 8                                                                                                                  |
|Query Identifier: -2358650375080684395                                                                                                                         |
|Planning Time: 0.421 ms                                                                                                                                        |
|Execution Time: 2.649 ms                                                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+

I then tried a similar solution using a lateral join, which yields the exact same results:

select u.id, z.zipcode, z.location, t.nearby_zipcodes
from t_user u
         join t_zipcode z on u.zipcode = z.zipcode
         cross join lateral (select array(select zz.zipcode
                                          from t_zipcode zz
                                          where st_dwithin(z.location, zz.location, 20000)
                                          order by zz.zipcode) as nearby_zipcodes
    ) t
order by u.id;

which to my surprise, has slightly better performance:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN                                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Sort  (cost=85823.88..85826.55 rows=1070 width=112) (actual time=1.828..1.830 rows=16 loops=1)                                                               |
|  Output: u.id, z.zipcode, z.location, ((SubPlan 1))                                                                                                         |
|  Sort Key: u.id                                                                                                                                             |
|  Sort Method: quicksort  Memory: 30kB                                                                                                                       |
|  ->  Nested Loop  (cost=0.29..85770.04 rows=1070 width=112) (actual time=0.319..1.809 rows=16 loops=1)                                                      |
|        Output: u.id, z.zipcode, z.location, (SubPlan 1)                                                                                                     |
|        Inner Unique: true                                                                                                                                   |
|        ->  Seq Scan on pg_temp.t_user u  (cost=0.00..20.70 rows=1070 width=48) (actual time=0.012..0.016 rows=16 loops=1)                                   |
|              Output: u.id, u.zipcode                                                                                                                        |
|        ->  Index Scan using test_idx_user_zip on pg_temp.t_zipcode z  (cost=0.29..0.65 rows=1 width=64) (actual time=0.006..0.006 rows=1 loops=16)          |
|              Output: z.zipcode, z.location                                                                                                                  |
|              Index Cond: (z.zipcode = u.zipcode)                                                                                                            |
|        SubPlan 1                                                                                                                                            |
|          ->  Sort  (cost=79.48..79.49 rows=3 width=32) (actual time=0.101..0.102 rows=17 loops=16)                                                          |
|                Output: zz.zipcode                                                                                                                           |
|                Sort Key: zz.zipcode                                                                                                                         |
|                Sort Method: quicksort  Memory: 25kB                                                                                                         |
|                ->  Index Scan using test_idx_zip_geo on pg_temp.t_zipcode zz  (cost=0.40..79.45 rows=3 width=32) (actual time=0.032..0.080 rows=17 loops=16)|
|                      Output: zz.zipcode                                                                                                                     |
|                      Index Cond: (zz.location && _st_expand(z.location, '20000'::double precision))                                                         |
|                      Filter: st_dwithin(z.location, zz.location, '20000'::double precision, true)                                                           |
|                      Rows Removed by Filter: 8                                                                                                              |
|Query Identifier: 8776498431209157273                                                                                                                        |
|Planning Time: 0.257 ms                                                                                                                                      |
|Execution Time: 1.892 ms                                                                                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

I was under the impression that lateral joins should be avoided in general when possible, is there maybe a demerit I'm failing to notice?

How does ON predicate of Postgres LATERAL JOIN work? Let me clarify question a bit. I've read the official documentation and a bunch of articles about this kind of JOIN. As far as I understood it is a foreach loop with a correlated subquery inside - it iterates over all records of a table A, allowing to reference columns of a "current" row in a correlated subquery B and join a result set of the B to that "current" row of A - if the B query returns 1 row there is only one pair, and if the B query return N rows there are N pairs with duplicated "current" row of the A. The same behavior like in usual JOINs. But why is there a need in ON predicate? For me, in usual JOINs we use ON because we have a cartesian product of 2 tables to be filtered out, and it is not the case of LATERAL JOIN, which produces resulting pairs directly. In other words, in my developer experience I've only seen CROSS JOIN LATERAL and LEFT JOIN LATERAL () ON TRUE (the latter looks quite clumsy, though) but one day a colleague showed me

SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT JOIN LATERAL (
    SELECT rts.route_id, array_agg(rts.shipment_id) shipment_ids
    FROM route_to_shipment rts
    where rts.route_id = r.route_id
    GROUP BY rts.route_id
) rts using (route_id)

and this exploded my mind. Why

(route_id)

? We already have

rts.route_id = r.route_id

inside the subquery!!! Maybe I understand the mechanics of LATERAL joins wrong?

First time posting here but long time reader. I'm fairly new in my role working on optimising sql queries. Most are generated from KNEX.js leading to some peculiar artifacts that might make sense in javascript but don't play well with SQL. I'm trying to improve the query below. The goal of the query is to produce a table listing a merchant's vending machines and the total amounts and counts taken through different gateways (eg AMEX, VISA, EBT, COUPON, NULL[Cash]) for accounting periods. I've reproduced it fully although I've altered the gateway names to ABC / XYZ for example purposes. Clearly there is plenty of potential for improvement but where I would specifically would like to ask for help is this: The query has two lateral joins that are essentially identical. vend_total and vend_partial. The only distinction is in the timestamp- and "vend"."timestamp" <= cash_accounting_period_ids.closed_at AS vend_total - or between cash_accounting_period_ids.created_at and cash_accounting_period_ids.closed_at AS vend_partial To my mind that makes vend_partial just a subset of vend_total. I would like to rewrite the query so that it does not have the obvious duplication of filters and does not fetch data twice, but I cannot wrap my head around how. It seems to me I should be able to reference vend_total as the basis of vend_partial instead of duplicating it, or instead rewriting the whole filter aggregate "totals" as a cte and then reference that, but tbh the complexity of this query is beating me. I'd really appreciate any suggestions people might have as to how to streamline this query, and more than that I'd love some recommendations for resources or strategies for query analysis and optimisation. I'm new to this and I enjoy the challenge but I work for a small startup and at this point I'm apparently the SQL expert so will take any advice I can get! George.

with "cash_accounting_period_ids" as (select "cash_accounting_period".*
                                      from "cash_accounting_period"
                                      where "cash_accounting_period"."merchant_id" = ?1
                                        and "cash_accounting_period"."closed_at" is not null
                                      order by closed_at DESC NULLS FIRST, cash_accounting_period.created_at DESC
                                      limit ?2)
select "cash_accounting_period_ids".*,
       (select row_to_json(vm)
        from (select vending_machine.*, ST_AsGeoJSON(location) as location) as vm) AS "vendingMachine",
       row_to_json("verified_by".*)                                                AS "verifiedBy",
       row_to_json("closed_by".*)                                                  AS "closedBy",
       row_to_json("company".*)                                                    AS "client",
       row_to_json(vend_total.*)                                                   AS "vendTotal",
       row_to_json(vend_partial.*)                                                 AS "vendPartial",
       row_to_json(route.*)                                                        AS route
from "cash_accounting_period_ids"
         left join "vending_machine" on "vending_machine"."id" = "cash_accounting_period_ids"."vending_machine_id"
         left join "user" AS "verified_by" on "verified_by"."id" = "cash_accounting_period_ids"."verified_by"
         left join "user" AS "closed_by" on "closed_by"."id" = "cash_accounting_period_ids"."closed_by"
         left join "company" on "company"."id" = "cash_accounting_period_ids"."client_id"
         left join lateral (select "totals".*
                            from (select "vend"."vending_machine_id",
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'ABC')           AS abc_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'ABC'),
                                                  0)                                                   AS abc_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'XYZ')           AS xyz_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'XYZ'),
                                                  0)                                                   AS xyz_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name IS NULL)             AS money_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name IS NULL),
                                                  0)                                                   AS money_amount,
                                         COUNT(*)                                                      AS total_count,
                                         COALESCE(SUM(vend.amount), 0)                                 AS total_amount
                                  from "vend"
                                           left join lateral (select coalesce(array_agg(route_vending_machine.route_id), '{}') as id
                                                              from "route_vending_machine"
                                                              where route_vending_machine.vending_machine_id = vend.vending_machine_id) subquery
                                                     ON TRUE
                                  where "vend"."merchant_id" = ?1
                                    and "vend"."display" = true
                                    and "vend"."currency_id" = 'EUR'
                                    and ("succeeded" = true or "succeeded" is null)
                                    and cash_accounting_period_ids.vending_machine_id = vend.vending_machine_id
                                    and (((TRUE or TRUE or TRUE)) and not (("subquery"."id" && '{}' or 1 = 0 or 1 = 0)))
                                    and "vend"."timestamp" <= cash_accounting_period_ids.closed_at
                                  group by "vend"."vending_machine_id", "vending_machine"."name") as "totals"
                            limit 1) vend_total ON TRUE
         left join lateral (select "totals".*
                            from (select "vend"."vending_machine_id",
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'ABC')           AS abc_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'ABC'),
                                                  0)                                                   AS abc_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name = 'XYZ')           AS xyz_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name = 'XYZ'),
                                                  0)                                                   AS xyz_amount,
                                         COUNT(*) FILTER (WHERE vend.gateway_name IS NULL)             AS money_count,
                                         COALESCE(SUM(vend.amount) FILTER (WHERE vend.gateway_name IS NULL),
                                                  0)                                                   AS money_amount,
                                         COUNT(*)                                                      AS total_count,
                                         COALESCE(SUM(vend.amount), 0)                                 AS total_amount
                                  from "vend"
                                           left join lateral (select coalesce(array_agg(route_vending_machine.route_id), '{}') as id
                                                              from "route_vending_machine"
                                                              where route_vending_machine.vending_machine_id = vend.vending_machine_id) subquery
                                                     ON TRUE
                                  where "vend"."merchant_id" = ?1
                                    and "vend"."display" = true
                                    and "vend"."currency_id" = 'EUR'
                                    and ("succeeded" = true or "succeeded" is null)
                                    and cash_accounting_period_ids.vending_machine_id = vend.vending_machine_id
                                    and (((TRUE or TRUE or TRUE)) and not (("subquery"."id" && '{}' or 1 = 0 or 1 = 0)))
                                    and "vend"."timestamp" between cash_accounting_period_ids.created_at and cash_accounting_period_ids.closed_at
                                  group by "vend"."vending_machine_id", "vending_machine"."name") as "totals"
                            limit 1) vend_partial ON TRUE
         left join "route_vending_machine"
                   on "route_vending_machine"."vending_machine_id" = "cash_accounting_period_ids"."vending_machine_id"
         left join "route" on "route"."id" = "route_vending_machine"."route_id";

EXPLAIN (ANALYSE, BUFFERS) is too long to share here in post or comment so I dumped it here: https://justpaste.it/bolah

Before all, sorry my very bad english level. Here's my problem. If i have two tables: 'Products' |id | product | |---|---------| |1 | "Fork" | |2 | "Spoon" | |3 | "Knife" | and 'taxes' |id | id_prod | tax | |---|---------|---------| |1 | 1 | 21 | |2 | 2 | 11,5 | If i execute the following command: SELECT product.*, taxes.tax FROM products LEFT JOIN taxes ON taxes.id_prod = products.id I will obtain this result. |id | product | tax | |---|---------|---------| |1 | "Fork" | 21 | |2 | "Spoon" | 11,5 | |3 | "Knife" | NULL | My question is: How i can give a default value when the product is not included in tax? I want to receive a result like this: If the default value is "21" |id | product | tax | |---|---------|---------| |1 | "Fork" | 21 | |2 | "Spoon" | 11,5 | |3 | "Knife" | 21 | How i can do that? Thanks in advance...

I am a beginner with PostgreSQL and have two tables A and B, both with multiple columns, including a point column (geom): TableA includes a given 'ID', geom, etc , TableB is a "DB" of locations with a 'name' column, geom, etc... **Need to find for each TableA.id what is the the 'name' (row columns) and distance of the closest location in TableB.** already could calculate the distance between all points of TableA and TableB with : ST_DistanceSpheroid(pointA,pointB,[SPHEROID]) But the best could build so far, is a select query where provides for each TAbleA.ID all distances and respective names from TableB - does not find the minimum for each ID only. **While needed to have the new calculated columns (distance and name) added to Table A.** It is not yet clear to me how to work with data output when using "select" vs an actual table data output. or use of views.

Suppose there's a product table with the following fields

id (pk), category (index), name, picture, stock

and we want to find ONE product in each of these category (1, 2, 3) that match at least one of the predefined rules in a way that, if there're products that match the first rule, we should pick one of them; otherwise we match against the second rule, and so on. For instance, suppose we have rules 1. picture IS NOT NULL AND stock > 0 2. stock > 0 3. picture IS NOT NULL 4. otherwise Currently I've figured out two solutions 1.

SELECT DISTINCT ON (category) id, category, name, picture, stock,
  CASE
    WHEN (picture IS NOT NULL AND stock > 0) THEN 0
    WHEN (stock > 0) THEN 1
    WHEN (picture IS NOT NULL) THEN 2
    ELSE 3
  END AS score
FROM product
WHERE category = ANY('{1, 2, 3}') AND deleted_at IS NULL
ORDER BY category, score

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=10.98..11.00 rows=4 width=84)
   ->  Sort  (cost=10.98..10.99 rows=4 width=84)
         Sort Key: category, (CASE WHEN (picture IS NOT NULL AND stock > 0) THEN 0 WHEN (stock > 0) THEN 1 WHEN (picture IS NOT NULL) THEN 2 ELSE 3 END)
         ->  Seq Scan on product  (cost=0.00..10.94 rows=4 width=84)
               Filter: ((deleted_at IS NULL) AND (category = ANY ('{1,2,3}'::bigint[])))

(I believe Seq Scan is used here because there're too few records in the table) 2.

WITH cte (category) AS (
  SELECT * FROM unnest('{1, 2, 3}'::BIGINT[])
)
SELECT * FROM cte
JOIN LATERAL (
  SELECT id, category, name, picture, stock,
    CASE
      WHEN (picture IS NOT NULL AND stock > 0) THEN 0
      WHEN (stock > 0) THEN 1
      WHEN (picture IS NOT NULL) THEN 2
      ELSE 3
    END AS score
  FROM product
  WHERE category = cte.category
  ORDER BY score
  LIMIT 1
) p ON true

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=9.88..39.63 rows=4 width=92)
   ->  Function Scan on unnest  (cost=0.00..0.04 rows=4 width=8)
   ->  Limit  (cost=9.87..9.88 rows=1 width=84)
         ->  Sort  (cost=9.87..9.88 rows=3 width=84)
               Sort Key: (CASE WHEN (picture IS NOT NULL AND stock > 0) THEN 0 WHEN (stock > 0) THEN 1 WHEN (picture IS NOT NULL) THEN 2 ELSE 3 END)
               ->  Bitmap Heap Scan on product  (cost=4.17..9.86 rows=3 width=84)
                     Recheck Cond: (category = unnest.unnest)
                     Filter: (deleted_at IS NULL)
                     ->  Bitmap Index Scan on product_category_idx  (cost=0.00..4.17 rows=3 width=0)
                           Index Cond: (category = unnest.unnest)

My questions are 1. Is the second solution faster than the first one (based on my understanding, the second solution sorts rows locally for each group, so it would be faster than the first one that sorts globally). 2. Currently, we encode rules into the integer space, so that we must first fetch all rows in those categories before sorting. How do we improve the query performance with the concept that **"if we find a record that matches the first rule, we don't need to continue the search"**?

I don't often see the ON clause used with the LATERAL joins (PostgreSQL 11+). For example, the official documentation has this example: > A trivial example of LATERAL is > > SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = > foo.bar_id) ss; > > This is not especially useful since it has exactly the same result as > the more conventional > > SELECT * FROM foo, bar WHERE bar.id = foo.bar_id; From the example, the equivalent conventional join has an ON clause (written using WHERE, WHERE bar.id = foo.bar_id), but for the LATERAL JOIN, the join condition seems to be "internalized". I feel that this example is not alone. I see many lateral join usages without an ON clause, but haven't seen much use with an ON clause. Conceptually, it's not clear whether an ON clause is necessary for a lateral join since each set of dependent values is only joined to the rows that they depended on. Using the next example from the same documentation to illustrate: > For example, supposing that vertices(polygon) returns the set of > vertices of a polygon, we could identify close-together vertices of > polygons stored in a table with: SELECT p1.id, p2.id, v1, v2 FROM polygons p1, polygons p2, LATERAL vertices(p1.poly) v1, LATERAL vertices(p2.poly) v2 WHERE (v1 v2) < 10 AND p1.id != p2.id; Here, the vertex set generated for each polygon p1.poly is only associated with that polygon alone, not any other polygons. There does not seem to be a need for specifying the correlation between the computed result and the original polygon using an ON clause. The "join condition" seems to be implied in the dependence of columns in the lateral join. I couldn't find confirmation from the linked documentation whether an ON-clause is necessary or even allowed for a LATERAL JOIN. Hence this question: *Does a LATERAL JOIN require/allow a join condition (an ON clause)?*