This is a really weird one… Running PostgreSQL 16.2.

I have this rather messy query, although not too complex from a join/logic perspective where two tables are joined, as:

select o.supplier as account_id, receiver_data->>'reference' as attention,
    case when powered_by = false or status != 'Sent' then null
    when credit_note = true or "references" @> '[{"credited": [{}]}]' then 0
    else paid_amount / coalesce (exchange_rate, 1) - total end
    as balance, jsonb_path_query_first("references", '$.credited[0].id') as credited_id, o.currency, receiver_data->>'company_name' as customer, due_date, send_method as format, case when credit_note = false then total else -total end as gross_amount, o.id, sent_at as invoice_date, document_id as invoice_no, paid_amount, payment_reference, payment_status, powered_by, sender_data->>'our_reference' as reference, status, case when credit_note = true then 'Credit' else 'Debit' end as type
    from outgoinginvoice o join supplier on supplier.id = o.supplier
    where supplier.organization_number = 'SOME_VALUE' order by COALESCE(o."sent_at", o."updatedAt") desc limit 1 offset 0;

The formatting and calculations for returning a result is a bit over the top (to say the least) but for now I am forced to return the data like that due to some legacy reasons…

However, this supplier has only one record in the outgoinginvoice table the weird thing here is that if I set LIMIT 1 it returns fine in a few milliseconds (ms), the same if I set LIMIT 100.

However, if I set LIMIT 3 (which unfortunately is the default from the frontend) it keeps on running the query forever (+10 minutes and then I cancelled it).

If I dumb it down to:

select o.supplier as account_id, receiver_data->>'reference' as attention,
    o.currency, receiver_data->>'company_name' as customer, due_date, send_method as format, o.id, sent_at as invoice_date, document_id as invoice_no, paid_amount, payment_reference, payment_status, powered_by, sender_data->>'our_reference' as reference, status
    from outgoinginvoice o join supplier on supplier.id = o.supplier 
    where supplier.organization_number = 'SOME_VALUE' order by COALESCE(o."sent_at", o."updatedAt") desc limit 3 offset 0;

It returns in about 4 seconds with LIMIT 3, but setting LIMIT 30 it runs in less than 100 ms.

Analyzing the two different queries I get:

# With limit 30:

Limit  (cost=17092.78..17092.86 rows=30 width=181) (actual time=0.604..0.605 rows=1 loops=1)
  ->  Sort  (cost=17092.78..17097.41 rows=1851 width=181) (actual time=0.603..0.604 rows=1 loops=1)
        Sort Key: (COALESCE(o.sent_at, o."updatedAt")) DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=0.43..17038.12 rows=1851 width=181) (actual time=0.078..0.597 rows=1 loops=1)
              ->  Seq Scan on supplier  (cost=0.00..176.76 rows=1 width=4) (actual time=0.027..0.546 rows=1 loops=1)
                    Filter: (organization_number = 'SOME_VALUE'::text)
                    Rows Removed by Filter: 3105
              ->  Index Scan using idx_outgoinginvoice_supplier on outgoinginvoice o  (cost=0.43..16469.81 rows=37766 width=628) (actual time=0.006..0.007 rows=1 loops=1)
                    Index Cond: (supplier = supplier.id)
Planning Time: 0.444 ms
Execution Time: 0.650 ms

While using LIMIT 3 I get:

Limit  (cost=0.71..2268.25 rows=3 width=181) (actual time=0.511..5606.588 rows=1 loops=1)
  ->  Nested Loop  (cost=0.71..1399071.25 rows=1851 width=181) (actual time=0.510..5606.586 rows=1 loops=1)
        Join Filter: (o.supplier = supplier.id)
        Rows Removed by Join Filter: 5732582
        ->  Index Scan Backward using idx_coal_outgoinginvoice_sent_at_updatedat on outgoinginvoice o  (cost=0.43..1312942.76 rows=5740420 width=628) (actual time=0.022..3127.632 rows=5732583 loops=1)
        ->  Materialize  (cost=0.28..8.30 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=5732583)
              ->  Index Scan using idx_supplier_organization_number on supplier  (cost=0.28..8.30 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1)
                    Index Cond: (organization_number = 'SOME_VALUE'::text)
Planning Time: 0.436 ms
Execution Time: 5606.639 ms