NESTED LOOP и GroupAggregate

Работаю с таким запросом (ниже приведу, лишь его часть, чтобы не перегружать):

SELECT t0.k0 AS jkey0,
       CASE
           WHEN t0.p0 IS NOT NULL THEN t0.p0
           WHEN (t1.e0 IS NOT NULL
                 AND t2.Sale_batchUserInvoiceDetail_UserInvoiceDetail IS NULL) THEN t1.e0
           ELSE NULL
       END AS jprop0
FROM t_10 t0
LEFT JOIN Sale_userInvoiceDetail t2 ON t2.key0=t0.k0
LEFT JOIN
  (SELECT t0.k0 AS k0,
          t1.PriceList_skuPriceListLedger_PriceListLedger AS k1,
          t0.k2 AS k2,
          LAST(t3.PriceList_pricePriceListLedgerLedgerPriceListType_PriceListLedg
               ORDER BY t1.PriceList_fromDateTimePriceListLedger_PriceListLedger ASC NULLS FIRST,t1.key0 ASC) AS e0
   FROM PriceList_priceListLedger t1
   JOIN
     (SELECT t0.Sale_supplierStockUserInvoiceDetail_null AS k0,
             t0.Sale_skuUserInvoiceDetail_UserInvoiceDetail AS k1,
             t0.Sale_dateTimeUserInvoiceDetail_null AS k2,
             ANYVALUE(1) AS e0
      FROM Sale_userInvoiceDetail t0
      JOIN t_10 t1 ON t1.k0=t0.key0
      WHERE (t0.Sale_supplierStockUserInvoiceDetail_null IS NOT NULL
             AND t0.Sale_skuUserInvoiceDetail_UserInvoiceDetail IS NOT NULL
             AND t0.Sale_dateTimeUserInvoiceDetail_null IS NOT NULL)
      GROUP BY 1,
               2,
               3) t0 ON t0.k1=t1.PriceList_skuPriceListLedger_PriceListLedger
   JOIN PriceList_priceListLedgerLedgerPriceListTypeStock t2 ON t2.key1=457
   AND t2.key2=t0.k0
   AND t2.key0=t1.key0
   LEFT JOIN PriceList_priceListLedgerLedgerPriceListType t3 ON t3.key0=t1.key0
   AND t3.key1=457
   WHERE ((t0.k2>t1.PriceList_fromDateTimePriceListLedger_PriceListLedger)
          AND ((t1.PriceList_toDateTimePriceListLedger_PriceListLedger IS NULL
                OR NOT t0.k2>t1.PriceList_toDateTimePriceListLedger_PriceListLedger))
          AND t2.PriceList_activePriceListLedgerLedgerPriceListTypeStock_null_nu IS NOT NULL
          AND t0.e0 IS NOT NULL)
   GROUP BY 1,
            2,
            3) t1 ON t1.k0=t2.Sale_supplierStockUserInvoiceDetail_null
AND t1.k1=t2.Sale_skuUserInvoiceDetail_UserInvoiceDetail
AND t1.k2=t2.Sale_dateTimeUserInvoiceDetail_null
LEFT JOIN PriceList_ledgerPriceListTypeStock t3 ON t3.key1=t2.Sale_supplierStockUserInvoiceDetail_null
AND t3.key0=457
LEFT JOIN Stock_batch t5 ON t5.key0=t2.Sale_batchUserInvoiceDetail_UserInvoiceDetail

План у него получается следующий (опять таки кусок касающийся этой части запроса):

65 t Insert on pg_temp_6.t_12  (cost=144875.29..144882.24 rows=327 width=49) (actual time=53453.065..53453.065 rows=0 loops=1)
65 t   ->  Merge Left Join  (cost=144875.29..144882.24 rows=327 width=49) (actual time=53440.192..53452.345 rows=327 loops=1)
65           Output: CASE WHEN (t0.p0 IS NOT NULL) THEN t0.p0 WHEN ((t1.e0 IS NOT NULL) AND (t2.sale_batchuserinvoicedetail_userinvoicedetail IS NULL)) THEN t1.e0 ELSE NULL::numeric END, t0.k0
66           Merge Cond: ((t2.sale_supplierstockuserinvoicedetail_null = t1.k0) AND (t2.sale_datetimeuserinvoicedetail_null = t1.k2))
66           Join Filter: (t1.k1 = t2.sale_skuuserinvoicedetail_userinvoicedetail)
66           Rows Removed by Join Filter: 104967
66           Filter: (((t1.e0 IS NOT NULL) AND (t2.sale_batchuserinvoicedetail_userinvoicedetail IS NULL)) OR ((t3.pricelist_batchledgerpricelisttypestock_ledgerpricelisttype_sto IS NULL) AND ((last(t3_1.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1_1.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1_1.key0)) IS NOT NULL)) OR (((last(t4.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t2_3.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1_3.key0)) IS NOT NULL) AND (t3.pricelist_batchledgerpricelisttypestock_ledgerpricelisttype_sto IS NOT NULL)) OR (t0.p0 IS NOT NULL))
66 t         ->  Merge Left Join  (cost=95041.82..95046.26 rows=327 width=101) (actual time=53249.709..53250.690 rows=327 loops=1)
66                 Output: t0.p0, t0.k0, t2.sale_batchuserinvoicedetail_userinvoicedetail, t2.sale_supplierstockuserinvoicedetail_null, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datetimeuserinvoicedetail_null, t3.pricelist_batchledgerpricelisttypestock_ledgerpricelisttype_sto, (last(t3_1.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1_1.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1_1.key0)), (last(t4.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t2_3.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1_3.key0))
67                 Merge Cond: ((t2.sale_supplierstockuserinvoicedetail_null = t0_2.sale_supplierstockuserinvoicedetail_null) AND (t2.sale_datetimeuserinvoicedetail_null = t0_2.sale_datetimeuserinvoicedetail_null) AND (t2.sale_batchuserinvoicedetail_userinvoicedetail = t0_2.sale_batchuserinvoicedetail_userinvoicedetail))
67 t               ->  Sort  (cost=50569.17..50569.99 rows=327 width=69) (actual time=53104.693..53104.702 rows=327 loops=1)
67                       Output: t0.p0, t0.k0, t2.sale_batchuserinvoicedetail_userinvoicedetail, t2.sale_supplierstockuserinvoicedetail_null, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datetimeuserinvoicedetail_null, t3.pricelist_batchledgerpricelisttypestock_ledgerpricelisttype_sto, (last(t3_1.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1_1.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1_1.key0))
67                       Sort Key: t2.sale_supplierstockuserinvoicedetail_null, t2.sale_datetimeuserinvoicedetail_null, t2.sale_batchuserinvoicedetail_userinvoicedetail
67                       Sort Method: quicksort  Memory: 70kB
68 t (!)                ->  Nested Loop Left Join  (cost=49972.27..50555.52 rows=327 width=69) (actual time=188.679..53104.349 rows=327 loops=1)
68                             Output: t0.p0, t0.k0, t2.sale_batchuserinvoicedetail_userinvoicedetail, t2.sale_supplierstockuserinvoicedetail_null, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datetimeuserinvoicedetail_null, t3.pricelist_batchledgerpricelisttypestock_ledgerpricelisttype_sto, (last(t3_1.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1_1.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1_1.key0))
68                             ->  Seq Scan on pg_temp_6.t_10 t0  (cost=0.00..5.27 rows=327 width=13) (actual time=0.004..0.300 rows=327 loops=1)
68                                   Output: t0.p0, t0.k0
68 t                           ->  Hash Right Join  (cost=49972.27..49974.03 rows=1 width=60) (actual time=161.978..162.363 rows=1 loops=327)
68                                   Output: t2.sale_batchuserinvoicedetail_userinvoicedetail, t2.key0, t2.sale_supplierstockuserinvoicedetail_null, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datetimeuserinvoicedetail_null, t3.pricelist_batchledgerpricelisttypestock_ledgerpricelisttype_sto, (last(t3_1.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1_1.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1_1.key0))
69                                   Hash Cond: ((t1_2.sale_supplierstockuserinvoicedetail_null = t2.sale_supplierstockuserinvoicedetail_null) AND (t1_1.pricelist_skupricelistledger_pricelistledger = t5.stock_skubatch_batch) AND (t1_2.sale_datetimeuserinvoicedetail_null = t2.sale_datetimeuserinvoicedetail_null))
69 t                                 ->  GroupAggregate  (cost=49964.12..49965.74 rows=6 width=35) (actual time=161.420..162.289 rows=322 loops=327 (!))
69                                         Output: t1_2.sale_supplierstockuserinvoicedetail_null, t1_1.pricelist_skupricelistledger_pricelistledger, t1_2.sale_datetimeuserinvoicedetail_null, last(t3_1.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1_1.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1_1.key0)
69 G                                       ->  Sort  (cost=49964.12..49964.14 rows=6 width=35) (actual time=161.388..161.400 rows=545 loops=327 (!))
69                                               Output: t1_2.sale_supplierstockuserinvoicedetail_null, t1_1.pricelist_skupricelistledger_pricelistledger, t1_2.sale_datetimeuserinvoicedetail_null, t3_1.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg, t1_1.pricelist_fromdatetimepricelistledger_pricelistledger, t1_1.key0
69                                               Sort Key: t1_2.sale_supplierstockuserinvoicedetail_null, t1_1.pricelist_skupricelistledger_pricelistledger, t1_2.sale_datetimeuserinvoicedetail_null
70                                               Sort Method: quicksort  Memory: 67kB
70 G                                             ->  Nested Loop Left Join  (cost=33564.15..49964.05 rows=6 width=35) (actual time=73.204..161.193 rows=545 loops=327)
70                                                     Output: t1_2.sale_supplierstockuserinvoicedetail_null, t1_1.pricelist_skupricelistledger_pricelistledger, t1_2.sale_datetimeuserinvoicedetail_null, t3_1.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg, t1_1.pricelist_fromdatetimepricelistledger_pricelistledger, t1_1.key0
70 G                                                   ->  Hash Join  (cost=33563.72..49927.02 rows=6 width=28) (actual time=73.190..159.776 rows=545 loops=327)
70                                                           Output: t1_1.pricelist_skupricelistledger_pricelistledger, t1_1.pricelist_fromdatetimepricelistledger_pricelistledger, t1_1.key0, t1_2.sale_supplierstockuserinvoicedetail_null, t1_2.sale_datetimeuserinvoicedetail_null
70                                                           Hash Cond: ((t2_1.key2 = t1_2.sale_supplierstockuserinvoicedetail_null) AND (t2_1.key0 = t1_1.key0))
71                                                           ->  Bitmap Heap Scan on public.pricelist_pricelistledgerledgerpricelisttypestock t2_1  (cost=6352.55..20172.45 rows=339112 width=8) (actual time=13.590..54.387 rows=341492 loops=327)
71                                                                 Output: t2_1.key0, t2_1.key1, t2_1.key2, t2_1.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu
71                                                                 Recheck Cond: (t2_1.key1 = 457)
71                                                                 Filter: (t2_1.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu IS NOT NULL)
71                                                                 ->  Bitmap Index Scan on pricelist_pricelistledgerledgerpricelisttypestock_key1_key2_idx  (cost=0.00..6267.77 rows=339112 width=0) (actual time=12.943..12.943 rows=341492 loops=327)
71                                                                       Index Cond: (t2_1.key1 = 457)
72 G                                                         ->  Hash  (cost=27147.84..27147.84 rows=4222 width=28) (actual time=20.204..20.204 rows=27765 loops=327)
72                                                                 Output: t1_1.pricelist_skupricelistledger_pricelistledger, t1_1.pricelist_fromdatetimepricelistledger_pricelistledger, t1_1.key0, t1_2.sale_supplierstockuserinvoicedetail_null, t1_2.sale_datetimeuserinvoicedetail_null
72                                                                 Buckets: 1024  Batches: 2 (originally 1)  Memory Usage: 1025kB
72 G                                                               ->  Nested Loop  (cost=2779.21..27147.84 rows=4222 width=28) (actual time=0.018..15.330 rows=27765 loops=327)
72                                                                       Output: t1_1.pricelist_skupricelistledger_pricelistledger, t1_1.pricelist_fromdatetimepricelistledger_pricelistledger, t1_1.key0, t1_2.sale_supplierstockuserinvoicedetail_null, t1_2.sale_datetimeuserinvoicedetail_null
72                                                                       ->  HashAggregate  (cost=2778.78..2782.05 rows=327 width=16) (actual time=0.009..0.090 rows=322 loops=327)
73                                                                             Output: t1_2.sale_supplierstockuserinvoicedetail_null, t0_1.stock_skubatch_batch, t1_2.sale_datetimeuserinvoicedetail_null, anyvalue(1)
73                                                                             Filter: (anyvalue(1) IS NOT NULL)
73                                                                             ->  Nested Loop  (cost=0.71..2612.83 rows=327 width=16) (actual time=0.023..2.452 rows=327 loops=1)
73                                                                                   Output: t1_2.sale_supplierstockuserinvoicedetail_null, t1_2.sale_datetimeuserinvoicedetail_null, t0_1.stock_skubatch_batch
73                                                                                   ->  Nested Loop  (cost=0.42..2480.42 rows=327 width=16) (actual time=0.016..0.587 rows=327 loops=1)
73                                                                                         Output: t1_2.sale_supplierstockuserinvoicedetail_null, t1_2.sale_datetimeuserinvoicedetail_null, t1_2.sale_batchuserinvoicedetail_userinvoicedetail
74                                                                                         ->  Seq Scan on pg_temp_6.t_10 t2_2  (cost=0.00..5.27 rows=327 width=4) (actual time=0.005..0.024 rows=327 loops=1)
74                                                                                               Output: t2_2.p0, t2_2.k0
74                                                                                         ->  Index Scan using pk_sale_userinvoicedetail on public.sale_userinvoicedetail t1_2  (cost=0.42..7.56 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=327)
74                                                                                               Output: t1_2.*
75                                                                                               Index Cond: (t1_2.key0 = t2_2.k0)
75                                                                                               Filter: ((t1_2.sale_supplierstockuserinvoicedetail_null IS NOT NULL) AND (t1_2.sale_datetimeuserinvoicedetail_null IS NOT NULL))
75                                                                                   ->  Index Scan using pk_stock_batch on public.stock_batch t0_1  (cost=0.29..0.39 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=327)
75                                                                                         Output: t0_1.*, ...
76                                                                                         Index Cond: (t0_1.key0 = t1_2.sale_batchuserinvoicedetail_userinvoicedetail)
76                                                                                         Filter: (t0_1.stock_skubatch_batch IS NOT NULL)
76                                                                       ->  Index Scan using pricelist_pricelistledger_pricelist_skupricelistledger_pricelis on public.pricelist_pricelistledger t1_1  (cost=0.43..74.37 rows=13 width=24) (actual time=0.003..0.042 rows=86 loops=105294)
76                                                                             Output: t1_1.key0, t1_1.pricelist_skupricelistledger_pricelistledger, t1_1.pricelist_ispostedpricelistledger_pricelistledger, t1_1.pricelist_todatetimepricelistledger_pricelistledger, t1_1.pricelist_fromdatetimepricelistledger_pricelistledger, t1_1.pricelist_skippricelistledger_pricelistledger, t1_1.pricelist_activepricelistledger_null, t1_1.pricelist_companypricelistledger_pricelistledger, t1_1.pricelist_descriptionpricelistledger_pricelistledger, t1_1.pricelist_companystockpricelistledger_pricelistledger
76                                                                             Index Cond: (t1_1.pricelist_skupricelistledger_pricelistledger = t0_1.stock_skubatch_batch)
76                                                                             Filter: ((t1_2.sale_datetimeuserinvoicedetail_null > t1_1.pricelist_fromdatetimepricelistledger_pricelistledger) AND ((t1_1.pricelist_todatetimepricelistledger_pricelistledger IS NULL) OR (t1_2.sale_datetimeuserinvoicedetail_null <= t1_1.pricelist_todatetimepricelistledger_pricelistledger)))
77                                                                             Rows Removed by Filter: 7
77                                                     ->  Index Scan using pk_pricelist_pricelistledgerledgerpricelisttype on public.pricelist_pricelistledgerledgerpricelisttype t3_1  (cost=0.43..6.16 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=178215)
77                                                           Output: t3_1.key0, t3_1.key1, t3_1.pricelist_inpricelistledgersystemledgerpricelisttype_pricelistl, t3_1.pricelist_inpricelistledgerledgerpricelisttype_pricelistledger_, t3_1.pricelist_pricepricelistledgersystemledgerpricelisttype_priceli, t3_1.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg, t3_1.pricelist_activepricelistledgerledgerpricelisttype_null_null
77                                                           Index Cond: ((t3_1.key0 = t1_1.key0) AND (t3_1.key1 = 457))
77                                   ->  Hash  (cost=8.13..8.13 rows=1 width=32) (actual time=0.031..0.031 rows=1 loops=327)
77                                         Output: t2.sale_batchuserinvoicedetail_userinvoicedetail, t2.key0, t2.sale_supplierstockuserinvoicedetail_null, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datetimeuserinvoicedetail_null, t3.pricelist_batchledgerpricelisttypestock_ledgerpricelisttype_sto, t5.stock_skubatch_batch
78                                         Buckets: 1024  Batches: 1  Memory Usage: 1kB
78                                         ->  Nested Loop Left Join  (cost=0.85..8.13 rows=1 width=32) (actual time=0.028..0.029 rows=1 loops=327)
78                                               Output: t2.sale_batchuserinvoicedetail_userinvoicedetail, t2.key0, t2.sale_supplierstockuserinvoicedetail_null, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datetimeuserinvoicedetail_null, t3.pricelist_batchledgerpricelisttypestock_ledgerpricelisttype_sto, t5.stock_skubatch_batch
78                                               ->  Nested Loop Left Join  (cost=0.56..7.73 rows=1 width=28) (actual time=0.013..0.014 rows=1 loops=327)
78                                                     Output: t2.sale_batchuserinvoicedetail_userinvoicedetail, t2.key0, t2.sale_supplierstockuserinvoicedetail_null, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datetimeuserinvoicedetail_null, t3.pricelist_batchledgerpricelisttypestock_ledgerpricelisttype_sto
79                                                     ->  Index Scan using pk_sale_userinvoicedetail on public.sale_userinvoicedetail t2  (cost=0.42..7.56 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=327)
79                                                           Output: t2.key0, ...
80                                                           Index Cond: (t2.key0 = t0.k0)
80                                                     ->  Index Scan using pk_pricelist_ledgerpricelisttypestock on public.pricelist_ledgerpricelisttypestock t3  (cost=0.14..0.16 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=327)
80                                                           Output: t3.key0, t3.key1, t3.pricelist_batchledgerpricelisttypestock_ledgerpricelisttype_sto
80                                                           Index Cond: ((t3.key0 = 457) AND (t3.key1 = t2.sale_supplierstockuserinvoicedetail_null))
80                                               ->  Index Scan using pk_stock_batch on public.stock_batch t5  (cost=0.29..0.39 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=327)
81                                                     Output: t5.key0, ...
81                                                     Index Cond: (t5.key0 = t2.sale_batchuserinvoicedetail_userinvoicedetail)

В сумме он выполняется 55 секунд. И происходит это из-за того что GroupAggregate выполняется 327 раз. Соответственно вопрос почему она не материализует GroupAggregate ? Cost же у него запредельный. Estimate маленький.

Или у PostgreSQL вообще нет такой возможности как materialize подзапроса и будет проблема в случае:

A (маленькая таблица, ключ k0) LJ B (огромная таблица, ключ k0, поле f0 без индекса) ON A.k0 = B.k0 LJ SQ (маленький но долго выполняемый подзапрос с ключом k0) ON SQ.k0 = B.f0

Потому как у нее 2 варианта

или высчитать SQ, а затем right join'ить с B что будет очень долго так как придется бежать по B (индекса там напомню нет, да и результат будет большой так что и индекс не поможет), после чего уже hash join'ить с A (план будет очень медленным)

или (то что мы видим тут) для всех A, ищем все записи B и для каждой (! так как materialize'а нет) из них выполняем SQ после чего right join'им результат с B (план также будет медленным из-за многочисленного выполнения SQ).

Nitro_Junkie,

Я вообще правильно понимаю что во втором случае (и собственно в первом), она должна сделать так

Hash Right Join
Hash Cond SQ.k0 = B.f0
GroupAggregate (допустим группирующий подзапрос с таблицей SQ)
Hash
Nested Left Join
Seq scan A
Index scan B on индекс по k0
Index cond : B.k0 = A.k0

Nitro_Junkie,

Hash Right Join
     Hash Cond SQ.k0 = B.f0
     GroupAggregate (допустим группирующий подзапрос с таблицей SQ)
     Hash
          Nested Left Join
               Seq scan A
               Index scan B on индекс по k0
                      Index cond : B.k0 = A.k0

Nitro_Junkie
Nitro_Junkie, Hash Right Join Hash Cond SQ.k0 = B.f0 GroupAggregate (допустим группирующий подзапрос с таблицей SQ) Hash Nested Left Join Seq scan A Index scan B on индекс по k0 Index cond : B.k0 = A.k0

Проверил на других запросах, именно так она и должна делать, но почему здесь ее клинит загадка...

Nitro_Junkie,

закиньте этот group by в WITH блок и будет вам материализация.

Но у меня есть идея почему он не материализует.
У вас функция ANYVALUE(1) случайно не volatile?

--Maxim Boguk
www.postgresql-consulting.ru

Maxim Boguk
Nitro_Junkie, закиньте этот group by в WITH блок и будет вам материализация. Но у меня есть идея почему он не материализует. У вас функция ANYVALUE(1) случайно не volatile? --Maxim Boguk www.postgresql-consulting.ru

CREATE OR REPLACE FUNCTION getAnyNotNull(ANYELEMENT, ANYELEMENT) RETURNS ANYELEMENT AS
$$
  SELECT CASE WHEN $1 = NULL THEN $2 ELSE $1 END;
$$ LANGUAGE SQL STRICT;

DROP AGGREGATE IF EXISTS ANYVALUE (anyelement) CASCADE;
CREATE AGGREGATE ANYVALUE (anyelement) (
  sfunc = getAnyNotNull,
  stype = anyelement
);

Я вообще наблюдаю странное явление природы. В приложении запрос показывает такой план, запускаю в pgAdmin план уже нормальный, чудо какое-то...

Воообще совет... в таких ситуациях зачастую проще помочь планировщику и частично зафиксировать нужный вам план через WITH (в тех частях где вам 100% materialize нужен).
Ну и понимать что с стандартными настройками базы она только до 8 joins разворачивает/сворачивает а дальше начинается GEQO и угадает он с планом или нет никто не знает.
Так что для сложных запросов еще полезно join_collapse_limit/from_collapse_limit поднять слегка.

--Maxim Boguk
www.postgresql-consulting.ru

Maxim Boguk

Воообще совет... в таких ситуациях зачастую проще помочь планировщику и частично зафиксировать нужный вам план через WITH (в тех частях где вам 100% materialize нужен). Ну и понимать что с стандартными настройками базы она только до 8 joins разворачивает/сворачивает а дальше начинается GEQO и угадает он с планом или нет никто не знает. Так что для сложных запросов еще полезно join_collapse_limit/from_collapse_limit поднять слегка. --Maxim Boguk www.postgresql-consulting.ru

Проблема в том что 100% materialize тяжеловато угадать.

Кстати словил ту же проблему на совсем простом запросе, то есть на GEQO не спишешь:

SELECT t0.k0 AS jkey0,
       t0.p0 AS jprop0
FROM t_12 t0
LEFT JOIN Sale_userInvoiceDetail t2 ON t2.key0=t0.k0
LEFT JOIN
  (SELECT t1.PriceList_skuPriceListLedger_PriceListLedger AS k0,
          t0.k1 AS k1,
          t0.k2 AS k2,
          LAST(t3.PriceList_pricePriceListLedgerLedgerPriceListType_PriceListLedg
               ORDER BY t1.PriceList_fromDateTimePriceListLedger_PriceListLedger ASC NULLS FIRST,t1.key0 ASC) AS e0
   FROM PriceList_priceListLedger t1
   JOIN
     (SELECT t0.Sale_skuUserInvoiceDetail_UserInvoiceDetail AS k0,
             t0.Sale_dateTimeUserInvoiceDetail_null AS k1,
             t0.Sale_supplierStockUserInvoiceDetail_null AS k2,
             ANYVALUE(1) AS e0
      FROM Sale_userInvoiceDetail t0
      JOIN t_12 t1 ON t1.k0=t0.key0
      WHERE (t0.Sale_skuUserInvoiceDetail_UserInvoiceDetail IS NOT NULL
             AND t0.Sale_dateTimeUserInvoiceDetail_null IS NOT NULL
             AND t0.Sale_supplierStockUserInvoiceDetail_null IS NOT NULL)
      GROUP BY 1,
               2,
               3) t0 ON t0.k0=t1.PriceList_skuPriceListLedger_PriceListLedger
   JOIN PriceList_priceListLedgerLedgerPriceListTypeStock t2 ON t2.key1=452
   AND t2.key2=t0.k2
   AND t2.key0=t1.key0
   LEFT JOIN PriceList_priceListLedgerLedgerPriceListType t3 ON t3.key1=452
   AND t3.key0=t1.key0
   WHERE ((t0.k1>t1.PriceList_fromDateTimePriceListLedger_PriceListLedger)
          AND ((t1.PriceList_toDateTimePriceListLedger_PriceListLedger IS NULL
                OR NOT t0.k1>t1.PriceList_toDateTimePriceListLedger_PriceListLedger))
          AND t2.PriceList_activePriceListLedgerLedgerPriceListTypeStock_null_nu IS NOT NULL
          AND t0.e0 IS NOT NULL)
   GROUP BY 1,
            2,
            3) t1 ON t1.k0=t2.Sale_skuUserInvoiceDetail_UserInvoiceDetail
AND t1.k1=t2.Sale_dateTimeUserInvoiceDetail_null
AND t1.k2=t2.Sale_supplierStockUserInvoiceDetail_null
WHERE ((t0.p0 IS NOT NULL
        OR t1.e0 IS NOT NULL)
       AND t2.Sale_batchUserInvoiceDetail_UserInvoiceDetail IS NULL);

И кстати понял почему в pgAdmin не повторялось. Я когда временную таблицу (t_12) сохранял в постоянную чтобы в pgAdmin запустить, для последней не сделал VACUUM ANALYZE. Тогда postgres думал что записей 1540, а не 327 и план правильный был:

"Nested Loop Left Join  (cost=92398.54..101478.73 rows=1 width=22) (actual time=0.508..0.508 rows=0 loops=1)"
"  Output: t0.k0, t0.p0"
"  Join Filter: ((t1.pricelist_skupricelistledger_pricelistledger = t2.sale_skuuserinvoicedetail_userinvoicedetail) AND (t0_1.sale_datetimeuserinvoicedetail_null = t2.sale_datetimeuserinvoicedetail_null) AND (t0_1.sale_supplierstockuserinvoicedetail_null =  (...)"
"  Filter: ((t0.p0 IS NOT NULL) OR ((last(t3.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1.key0)) IS NOT NULL))"
"  ->  Nested Loop Left Join  (cost=0.42..9072.27 rows=1 width=38) (actual time=0.508..0.508 rows=0 loops=1)"
"        Output: t0.k0, t0.p0, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datetimeuserinvoicedetail_null, t2.sale_supplierstockuserinvoicedetail_null"
"        Filter: (t2.sale_batchuserinvoicedetail_userinvoicedetail IS NULL)"
"        Rows Removed by Filter: 327"
"        ->  Seq Scan on public.t_12 t0  (cost=0.00..25.70 rows=1570 width=22) (actual time=0.017..0.034 rows=327 loops=1)"
"              Output: t0.k0, t0.p0"
"        ->  Index Scan using pk_sale_userinvoicedetail on public.sale_userinvoicedetail t2  (cost=0.42..5.75 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=327)"
"              Output: t2.key0, t2.sale_userinvoiceuserinvoicedetail_userinvoicedetail, t2.sale_quantityuserinvoicedetail_userinvoicedetail, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datacustomerstockuserinvoicedetail_userinvoicedetail, t2.sal (...)"
"              Index Cond: (t2.key0 = t0.k0)"
"  ->  GroupAggregate  (cost=92398.12..92405.68 rows=28 width=35) (never executed)"
"        Output: t1.pricelist_skupricelistledger_pricelistledger, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null, last(t3.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1.pricelist_fromdate (...)"
"        ->  Sort  (cost=92398.12..92398.19 rows=28 width=35) (never executed)"
"              Output: t1.pricelist_skupricelistledger_pricelistledger, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null, t3.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg, t1.pricelist_fromdatetimepri (...)"
"              Sort Key: t1.pricelist_skupricelistledger_pricelistledger, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"              ->  Nested Loop Left Join  (cost=75723.31..92397.45 rows=28 width=35) (never executed)"
"                    Output: t1.pricelist_skupricelistledger_pricelistledger, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null, t3.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg, t1.pricelist_fromdatet (...)"
"                    ->  Hash Join  (cost=75722.88..92224.64 rows=28 width=28) (never executed)"
"                          Output: t1.pricelist_skupricelistledger_pricelistledger, t1.pricelist_fromdatetimepricelistledger_pricelistledger, t1.key0, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"                          Hash Cond: ((t2_1.key2 = t0_1.sale_supplierstockuserinvoicedetail_null) AND (t2_1.key0 = t1.key0))"
"                          ->  Bitmap Heap Scan on public.pricelist_pricelistledgerledgerpricelisttypestock t2_1  (cost=6482.11..20388.41 rows=346024 width=8) (never executed)"
"                                Output: t2_1.key0, t2_1.key1, t2_1.key2, t2_1.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu"
"                                Recheck Cond: (t2_1.key1 = 452)"
"                                Filter: (t2_1.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu IS NOT NULL)"
"                                ->  Bitmap Index Scan on pricelist_pricelistledgerledgerpricelisttypestock_key1_key2_idx  (cost=0.00..6395.61 rows=346024 width=0) (never executed)"
"                                      Index Cond: (t2_1.key1 = 452)"
"                          ->  Hash  (cost=68936.68..68936.68 rows=20272 width=28) (never executed)"
"                                Output: t1.pricelist_skupricelistledger_pricelistledger, t1.pricelist_fromdatetimepricelistledger_pricelistledger, t1.key0, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"                                ->  Hash Join  (cost=62197.79..68936.68 rows=20272 width=28) (never executed)"
"                                      Output: t1.pricelist_skupricelistledger_pricelistledger, t1.pricelist_fromdatetimepricelistledger_pricelistledger, t1.key0, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"                                      Hash Cond: (t0_1.sale_skuuserinvoicedetail_userinvoicedetail = t1.pricelist_skupricelistledger_pricelistledger)"
"                                      Join Filter: ((t0_1.sale_datetimeuserinvoicedetail_null > t1.pricelist_fromdatetimepricelistledger_pricelistledger) AND ((t1.pricelist_todatetimepricelistledger_pricelistledger IS NULL) OR (t0_1.sale_datetimeuserinvoic (...)"
"                                      ->  HashAggregate  (cost=9872.98..9888.68 rows=1570 width=16) (never executed)"
"                                            Output: t0_1.sale_skuuserinvoicedetail_userinvoicedetail, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null, anyvalue(1)"
"                                            Filter: (anyvalue(1) IS NOT NULL)"
"                                            ->  Nested Loop  (cost=0.42..9076.20 rows=1570 width=16) (never executed)"
"                                                  Output: t0_1.sale_skuuserinvoicedetail_userinvoicedetail, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"                                                  ->  Seq Scan on public.t_12 t1_1  (cost=0.00..25.70 rows=1570 width=4) (never executed)"
"                                                        Output: t1_1.k0, t1_1.p0"
"                                                  ->  Index Scan using pk_sale_userinvoicedetail on public.sale_userinvoicedetail t0_1  (cost=0.42..5.75 rows=1 width=20) (never executed)"
"                                                        Output: t0_1.key0, t0_1.sale_userinvoiceuserinvoicedetail_userinvoicedetail, t0_1.sale_quantityuserinvoicedetail_userinvoicedetail, t0_1.sale_skuuserinvoicedetail_userinvoicedetail, t0_1.sale_datacust (...)"
"                                                        Index Cond: (t0_1.key0 = t1_1.k0)"
"                                                        Filter: ((t0_1.sale_skuuserinvoicedetail_userinvoicedetail IS NOT NULL) AND (t0_1.sale_datetimeuserinvoicedetail_null IS NOT NULL) AND (t0_1.sale_supplierstockuserinvoicedetail_null IS NOT NULL))"
"                                      ->  Hash  (cost=36739.92..36739.92 rows=1246792 width=24) (never executed)"
"                                            Output: t1.pricelist_skupricelistledger_pricelistledger, t1.pricelist_fromdatetimepricelistledger_pricelistledger, t1.key0, t1.pricelist_todatetimepricelistledger_pricelistledger"
"                                            ->  Seq Scan on public.pricelist_pricelistledger t1  (cost=0.00..36739.92 rows=1246792 width=24) (never executed)"
"                                                  Output: t1.pricelist_skupricelistledger_pricelistledger, t1.pricelist_fromdatetimepricelistledger_pricelistledger, t1.key0, t1.pricelist_todatetimepricelistledger_pricelistledger"
"                    ->  Index Scan using pk_pricelist_pricelistledgerledgerpricelisttype on public.pricelist_pricelistledgerledgerpricelisttype t3  (cost=0.43..6.16 rows=1 width=11) (never executed)"
"                          Output: t3.key0, t3.key1, t3.pricelist_inpricelistledgersystemledgerpricelisttype_pricelistl, t3.pricelist_inpricelistledgerledgerpricelisttype_pricelistledger_, t3.pricelist_pricepricelistledgersystemledgerpricelisttype_priceli,  (...)"
"                          Index Cond: ((t3.key0 = t1.key0) AND (t3.key1 = 452))"
"Total runtime: 0.716 ms"

Сделал VACUUM ANALYZE для этой t_12 и план стал :

"Nested Loop Left Join  (cost=50107.10..50690.34 rows=1 width=10) (actual time=56442.064..56442.064 rows=0 loops=1)"
"  Output: t0.k0, t0.p0"
"  Filter: (((t0.p0 IS NOT NULL) OR ((last(t3.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1.key0)) IS NOT NULL)) AND (t2.sale_batchuserinvoicedetail_userinvo (...)"
"  Rows Removed by Filter: 327"
"  ->  Seq Scan on public.t_12 t0  (cost=0.00..5.27 rows=327 width=10) (actual time=0.004..0.255 rows=327 loops=1)"
"        Output: t0.k0, t0.p0"
"  ->  Hash Right Join  (cost=50107.10..50108.85 rows=1 width=40) (actual time=172.189..172.581 rows=1 loops=327)"
"        Output: t2.key0, t2.sale_batchuserinvoicedetail_userinvoicedetail, (last(t3.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1.key0))"
"        Hash Cond: ((t1.pricelist_skupricelistledger_pricelistledger = t2.sale_skuuserinvoicedetail_userinvoicedetail) AND (t0_1.sale_datetimeuserinvoicedetail_null = t2.sale_datetimeuserinvoicedetail_null) AND (t0_1.sale_supplierstockuserinvoicedetail_nul (...)"
"        ->  GroupAggregate  (cost=50099.52..50101.14 rows=6 width=35) (actual time=171.638..172.528 rows=322 loops=327)"
"              Output: t1.pricelist_skupricelistledger_pricelistledger, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null, last(t3.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1.pricelist_fr (...)"
"              ->  Sort  (cost=50099.52..50099.54 rows=6 width=35) (actual time=171.599..171.613 rows=557 loops=327)"
"                    Output: t1.pricelist_skupricelistledger_pricelistledger, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null, t3.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg, t1.pricelist_fromdatet (...)"
"                    Sort Key: t1.pricelist_skupricelistledger_pricelistledger, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"                    Sort Method: quicksort  Memory: 68kB"
"                    ->  Nested Loop Left Join  (cost=33561.30..50099.45 rows=6 width=35) (actual time=21.949..171.428 rows=557 loops=327)"
"                          Output: t1.pricelist_skupricelistledger_pricelistledger, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null, t3.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg, t1.pricelist_fro (...)"
"                          ->  Hash Join  (cost=33560.87..50062.41 rows=6 width=28) (actual time=21.936..170.151 rows=557 loops=327)"
"                                Output: t1.pricelist_skupricelistledger_pricelistledger, t1.pricelist_fromdatetimepricelistledger_pricelistledger, t1.key0, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"                                Hash Cond: ((t2_1.key2 = t0_1.sale_supplierstockuserinvoicedetail_null) AND (t2_1.key0 = t1.key0))"
"                                ->  Bitmap Heap Scan on public.pricelist_pricelistledgerledgerpricelisttypestock t2_1  (cost=6482.11..20388.41 rows=346024 width=8) (actual time=13.324..42.777 rows=346794 loops=327)"
"                                      Output: t2_1.key0, t2_1.key1, t2_1.key2, t2_1.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu"
"                                      Recheck Cond: (t2_1.key1 = 452)"
"                                      Filter: (t2_1.pricelist_activepricelistledgerledgerpricelisttypestock_null_nu IS NOT NULL)"
"                                      ->  Bitmap Index Scan on pricelist_pricelistledgerledgerpricelisttypestock_key1_key2_idx  (cost=0.00..6395.61 rows=346024 width=0) (actual time=13.112..13.112 rows=346794 loops=327)"
"                                            Index Cond: (t2_1.key1 = 452)"
"                                ->  Hash  (cost=27015.43..27015.43 rows=4222 width=28) (actual time=26.120..26.120 rows=27765 loops=1)"
"                                      Output: t1.pricelist_skupricelistledger_pricelistledger, t1.pricelist_fromdatetimepricelistledger_pricelistledger, t1.key0, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 1844kB"
"                                      ->  Nested Loop  (cost=2646.80..27015.43 rows=4222 width=28) (actual time=0.644..21.293 rows=27765 loops=1)"
"                                            Output: t1.pricelist_skupricelistledger_pricelistledger, t1.pricelist_fromdatetimepricelistledger_pricelistledger, t1.key0, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"                                            ->  HashAggregate  (cost=2646.37..2649.64 rows=327 width=16) (actual time=0.636..0.719 rows=322 loops=1)"
"                                                  Output: t0_1.sale_skuuserinvoicedetail_userinvoicedetail, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null, anyvalue(1)"
"                                                  Filter: (anyvalue(1) IS NOT NULL)"
"                                                  ->  Nested Loop  (cost=0.42..2480.42 rows=327 width=16) (actual time=0.013..0.485 rows=327 loops=1)"
"                                                        Output: t0_1.sale_skuuserinvoicedetail_userinvoicedetail, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"                                                        ->  Seq Scan on public.t_12 t1_1  (cost=0.00..5.27 rows=327 width=4) (actual time=0.004..0.015 rows=327 loops=1)"
"                                                              Output: t1_1.k0, t1_1.p0"
"                                                        ->  Index Scan using pk_sale_userinvoicedetail on public.sale_userinvoicedetail t0_1  (cost=0.42..7.56 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=327)"
"                                                              Output: t0_1.key0, t0_1.sale_userinvoiceuserinvoicedetail_userinvoicedetail, t0_1.sale_quantityuserinvoicedetail_userinvoicedetail, t0_1.sale_skuuserinvoicedetail_userinvoicedetail, t0_1.sale_da (...)"
"                                                              Index Cond: (t0_1.key0 = t1_1.k0)"
"                                                              Filter: ((t0_1.sale_skuuserinvoicedetail_userinvoicedetail IS NOT NULL) AND (t0_1.sale_datetimeuserinvoicedetail_null IS NOT NULL) AND (t0_1.sale_supplierstockuserinvoicedetail_null IS NOT NULL) (...)"
"                                            ->  Index Scan using pricelist_pricelistledger_pricelist_skupricelistledger_pricelis on public.pricelist_pricelistledger t1  (cost=0.43..74.37 rows=13 width=24) (actual time=0.003..0.055 rows=86 loops=322)"
"                                                  Output: t1.key0, t1.pricelist_skupricelistledger_pricelistledger, t1.pricelist_ispostedpricelistledger_pricelistledger, t1.pricelist_todatetimepricelistledger_pricelistledger, t1.pricelist_fromdatetimeprice (...)"
"                                                  Index Cond: (t1.pricelist_skupricelistledger_pricelistledger = t0_1.sale_skuuserinvoicedetail_userinvoicedetail)"
"                                                  Filter: ((t0_1.sale_datetimeuserinvoicedetail_null > t1.pricelist_fromdatetimepricelistledger_pricelistledger) AND ((t1.pricelist_todatetimepricelistledger_pricelistledger IS NULL) OR (t0_1.sale_datetimeuse (...)"
"                                                  Rows Removed by Filter: 7"
"                          ->  Index Scan using pk_pricelist_pricelistledgerledgerpricelisttype on public.pricelist_pricelistledgerledgerpricelisttype t3  (cost=0.43..6.16 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=182139)"
"                                Output: t3.key0, t3.key1, t3.pricelist_inpricelistledgersystemledgerpricelisttype_pricelistl, t3.pricelist_inpricelistledgerledgerpricelisttype_pricelistledger_, t3.pricelist_pricepricelistledgersystemledgerpricelisttype_pri (...)"
"                                Index Cond: ((t3.key0 = t1.key0) AND (t3.key1 = 452))"
"        ->  Hash  (cost=7.56..7.56 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=327)"
"              Output: t2.key0, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datetimeuserinvoicedetail_null, t2.sale_supplierstockuserinvoicedetail_null, t2.sale_batchuserinvoicedetail_userinvoicedetail"
"              Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"              ->  Index Scan using pk_sale_userinvoicedetail on public.sale_userinvoicedetail t2  (cost=0.42..7.56 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=327)"
"                    Output: t2.key0, t2.sale_skuuserinvoicedetail_userinvoicedetail, t2.sale_datetimeuserinvoicedetail_null, t2.sale_supplierstockuserinvoicedetail_null, t2.sale_batchuserinvoicedetail_userinvoicedetail"
"                    Index Cond: (t2.key0 = t0.k0)"
"Total runtime: 56442.649 ms"

Nitro_Junkie,

Хм... Помогает уменьшение join_collapse_limit до 2:

"Nested Loop Left Join  (cost=52695.12..55176.09 rows=1 width=10) (actual time=0.609..0.609 rows=0 loops=1)"
"  Join Filter: ((t1.pricelist_skupricelistledger_pricelistledger = t2.sale_skuuserinvoicedetail_userinvoicedetail) AND (t0_1.sale_datetimeuserinvoicedetail_null = t2.sale_datetimeuserinvoicedetail_null) AND (t0_1.sale_supplierstockuserinvoicedetail_null =  (...)"
"  Filter: ((t0.p0 IS NOT NULL) OR ((last(t3.pricelist_pricepricelistledgerledgerpricelisttype_pricelistledg ORDER BY t1.pricelist_fromdatetimepricelistledger_pricelistledger NULLS FIRST, t1.key0)) IS NOT NULL))"
"  ->  Nested Loop Left Join  (cost=0.42..2479.60 rows=1 width=26) (actual time=0.607..0.607 rows=0 loops=1)"
"        Filter: (t2.sale_batchuserinvoicedetail_userinvoicedetail IS NULL)"
"        Rows Removed by Filter: 327"
"        ->  Seq Scan on t_12 t0  (cost=0.00..5.27 rows=327 width=10) (actual time=0.020..0.044 rows=327 loops=1)"
"        ->  Index Scan using pk_sale_userinvoicedetail on sale_userinvoicedetail t2  (cost=0.42..7.56 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=327)"
"              Index Cond: (key0 = t0.k0)"
"  ->  GroupAggregate  (cost=52694.70..52696.32 rows=6 width=35) (never executed)"
"        ->  Sort  (cost=52694.70..52694.72 rows=6 width=35) (never executed)"
"              Sort Key: t1.pricelist_skupricelistledger_pricelistledger, t0_1.sale_datetimeuserinvoicedetail_null, t0_1.sale_supplierstockuserinvoicedetail_null"
"              ->  Nested Loop Left Join  (cost=33561.30..52694.63 rows=6 width=35) (never executed)"
"                    ->  Hash Join  (cost=33560.87..52657.59 rows=6 width=28) (never executed)"
"                          Hash Cond: ((t2_1.key2 = t0_1.sale_supplierstockuserinvoicedetail_null) AND (t2_1.key0 = t1.key0))"
"                          ->  Bitmap Heap Scan on pricelist_pricelistledgerledgerpricelisttypestock t2_1  (cost=6482.11..20388.41 rows=346024 width=8) (never executed)"
"                                Recheck Cond: (key1 = 452)"
"                                Filter: (pricelist_activepricelistledgerledgerpricelisttypestock_null_nu IS NOT NULL)"
"                                ->  Bitmap Index Scan on pricelist_pricelistledgerledgerpricelisttypestock_key1_key2_idx  (cost=0.00..6395.61 rows=346024 width=0) (never executed)"
"                                      Index Cond: (key1 = 452)"
"                          ->  Hash  (cost=27015.43..27015.43 rows=4222 width=28) (never executed)"
"                                ->  Nested Loop  (cost=2646.80..27015.43 rows=4222 width=28) (never executed)"
"                                      ->  HashAggregate  (cost=2646.37..2649.64 rows=327 width=16) (never executed)"
"                                            Filter: (anyvalue(1) IS NOT NULL)"
"                                            ->  Nested Loop  (cost=0.42..2480.42 rows=327 width=16) (never executed)"
"                                                  ->  Seq Scan on t_12 t1_1  (cost=0.00..5.27 rows=327 width=4) (never executed)"
"                                                  ->  Index Scan using pk_sale_userinvoicedetail on sale_userinvoicedetail t0_1  (cost=0.42..7.56 rows=1 width=20) (never executed)"
"                                                        Index Cond: (key0 = t1_1.k0)"
"                                                        Filter: ((sale_skuuserinvoicedetail_userinvoicedetail IS NOT NULL) AND (sale_datetimeuserinvoicedetail_null IS NOT NULL) AND (sale_supplierstockuserinvoicedetail_null IS NOT NULL))"
"                                      ->  Index Scan using pricelist_pricelistledger_pricelist_skupricelistledger_pricelis on pricelist_pricelistledger t1  (cost=0.43..74.37 rows=13 width=24) (never executed)"
"                                            Index Cond: (pricelist_skupricelistledger_pricelistledger = t0_1.sale_skuuserinvoicedetail_userinvoicedetail)"
"                                            Filter: ((t0_1.sale_datetimeuserinvoicedetail_null > pricelist_fromdatetimepricelistledger_pricelistledger) AND ((pricelist_todatetimepricelistledger_pricelistledger IS NULL) OR (t0_1.sale_datetimeuserinvoicedeta (...)"
"                    ->  Index Scan using pk_pricelist_pricelistledgerledgerpricelisttype on pricelist_pricelistledgerledgerpricelisttype t3  (cost=0.43..6.16 rows=1 width=11) (never executed)"
"                          Index Cond: ((key0 = t1.key0) AND (key1 = 452))"
"Total runtime: 0.806 ms"

Хотя в общем-то логично что помогает, но управлять порядком join'ов мягко говоря не хочется.

И в принципе если взглянуть на 2 плана то у неэффективного cost ниже. Что очень странно с учетом того что она знает что ей нужно 327 loops сделать, и почему-то cost GroupAggregate'а она не умножает на 327 :(