|
Pavel
Stehule wrote: >
> 2017-09-12 8:45 GMT+02:00 Frank Millman <frank@xxxxxxxxxxxx>:
>
> please send result of explain analyze
>
> you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher
number 14 maybe 16
>
I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.
I have attached files containing my SQL command, and the results of EXPLAIN
ANALYSE
Frank
|
SELECT a.row_id,
(SELECT (b.inv_net_tot_cus + b.inv_tax_tot_cus + b.crn_net_tot_cus + b.crn_tax_tot_cus
+ b.jnl_tot_cus + b.rec_net_tot_cus + b.rec_dsc_tot_cus + b.rec_dtx_tot_cus)
FROM ccc.ar_cust_totals b
WHERE b.cust_row_id = a.row_id AND b.tran_date <= '2015-09-30'
ORDER BY b.tran_date DESC LIMIT 1)
as "balance_cust AS [DECTEXT]",
COALESCE(SUM(due_curr.amount_cust), 0) +
COALESCE(SUM(alloc_curr.alloc_cust + alloc_curr.disc_cust), 0)
AS "bal_cust_curr AS [DECTEXT]",
COALESCE(SUM(due_30.amount_cust), 0) +
COALESCE(SUM(alloc_30.alloc_cust + alloc_30.disc_cust), 0)
AS "bal_cust_30 AS [DECTEXT]",
COALESCE(SUM(due_60.amount_cust), 0) +
COALESCE(SUM(alloc_60.alloc_cust + alloc_60.disc_cust), 0)
AS "bal_cust_60 AS [DECTEXT]",
COALESCE(SUM(due_90.amount_cust), 0) +
COALESCE(SUM(alloc_90.alloc_cust + alloc_90.disc_cust), 0)
AS "bal_cust_90 AS [DECTEXT]",
COALESCE(SUM(due_120.amount_cust), 0) +
COALESCE(SUM(alloc_120.alloc_cust + alloc_120.disc_cust), 0)
AS "bal_cust_120 AS [DECTEXT]"
FROM ccc.ar_customers a
LEFT JOIN ccc.ar_trans trans ON trans.cust_row_id = a.row_id
LEFT JOIN ccc.ar_trans_due due_curr ON
due_curr.tran_type = trans.tran_type AND due_curr.tran_row_id = trans.tran_row_id
AND trans.tran_date > '2015-08-31'
LEFT JOIN ccc.ar_trans_alloc alloc_curr ON
alloc_curr.due_row_id = due_curr.row_id
LEFT JOIN ccc.ar_trans trans_alloc_curr ON
trans_alloc_curr.tran_type = alloc_curr.tran_type AND
trans_alloc_curr.tran_row_id = alloc_curr.tran_row_id AND
trans_alloc_curr.tran_date <= '2015-09-30'
LEFT JOIN ccc.ar_trans_due due_30 ON
due_30.tran_type = trans.tran_type AND due_30.tran_row_id = trans.tran_row_id
AND trans.tran_date > '2015-07-31' AND trans.tran_date <= '2015-08-31'
LEFT JOIN ccc.ar_trans_alloc alloc_30 ON
alloc_30.due_row_id = due_30.row_id
LEFT JOIN ccc.ar_trans trans_alloc_30 ON
trans_alloc_30.tran_type = alloc_30.tran_type AND
trans_alloc_30.tran_row_id = alloc_30.tran_row_id AND
trans_alloc_30.tran_date <= '2015-09-30'
LEFT JOIN ccc.ar_trans_due due_60 ON
due_60.tran_type = trans.tran_type AND due_60.tran_row_id = trans.tran_row_id
AND trans.tran_date > '2015-06-30' AND trans.tran_date <= '2015-07-31'
LEFT JOIN ccc.ar_trans_alloc alloc_60 ON
alloc_60.due_row_id = due_60.row_id
LEFT JOIN ccc.ar_trans trans_alloc_60 ON
trans_alloc_60.tran_type = alloc_60.tran_type AND
trans_alloc_60.tran_row_id = alloc_60.tran_row_id AND
trans_alloc_60.tran_date <= '2015-09-30'
LEFT JOIN ccc.ar_trans_due due_90 ON
due_90.tran_type = trans.tran_type AND due_90.tran_row_id = trans.tran_row_id
AND trans.tran_date > '2015-05-31' AND trans.tran_date <= '2015-06-30'
LEFT JOIN ccc.ar_trans_alloc alloc_90 ON
alloc_90.due_row_id = due_90.row_id
LEFT JOIN ccc.ar_trans trans_alloc_90 ON
trans_alloc_90.tran_type = alloc_90.tran_type AND
trans_alloc_90.tran_row_id = alloc_90.tran_row_id AND
trans_alloc_90.tran_date <= '2015-09-30'
LEFT JOIN ccc.ar_trans_due due_120 ON
due_120.tran_type = trans.tran_type AND due_120.tran_row_id = trans.tran_row_id
AND trans.tran_date <= '2015-05-31'
LEFT JOIN ccc.ar_trans_alloc alloc_120 ON
alloc_120.due_row_id = due_120.row_id
LEFT JOIN ccc.ar_trans trans_alloc_120 ON
trans_alloc_120.tran_type = alloc_120.tran_type AND
trans_alloc_120.tran_row_id = alloc_120.tran_row_id AND
trans_alloc_120.tran_date <= '2015-09-30'
WHERE a.ledger_row_id = ? AND a.party_row_id = ? AND a.deleted_id = ?
GROUP BY a.row_id
('HashAggregate (cost=11123.83..11211.17 rows=1 width=234) (actual time=299.781..299.782 rows=1 loops=1)',)
(' Group Key: a.row_id',)
(' -> Hash Right Join (cost=9833.36..11122.59 rows=31 width=234) (actual time=295.962..296.496 rows=1801 loops=1)',)
(' Hash Cond: (("*SELECT* 1_5".tran_type = (alloc_120.tran_type)::text) AND ("*SELECT* 1_5".tran_row_id = alloc_120.tran_row_id))',)
(' -> Append (cost=0.00..1019.01 rows=21603 width=36) (actual time=0.025..33.021 rows=21601 loops=1)',)
(' -> Subquery Scan on "*SELECT* 1_5" (cost=0.00..1000.03 rows=21601 width=36) (actual time=0.025..30.075 rows=21601 loops=1)',)
(' -> Seq Scan on ar_tran_inv (cost=0.00..784.02 rows=21601 width=46) (actual time=0.024..25.627 rows=21601 loops=1)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date) AND (deleted_id = 0))",)
(' -> Subquery Scan on "*SELECT* 2_5" (cost=4.13..9.49 rows=1 width=36) (actual time=0.008..0.008 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_crn (cost=4.13..9.48 rows=1 width=124) (actual time=0.007..0.007 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date))",)
(' -> Bitmap Index Scan on _ar_tran_crn (cost=0.00..4.13 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=1)',)
(' -> Subquery Scan on "*SELECT* 3_5" (cost=4.13..9.49 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_rec (cost=4.13..9.48 rows=1 width=124) (actual time=0.008..0.008 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date))",)
(' -> Bitmap Index Scan on _ar_tran_rec (cost=0.00..4.13 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=1)',)
(' -> Hash (cost=9832.90..9832.90 rows=31 width=270) (actual time=258.794..258.794 rows=1801 loops=1)',)
(' Buckets: 1024 Batches: 1 Memory Usage: 59kB',)
(' -> Nested Loop Left Join (cost=8536.93..9832.90 rows=31 width=270) (actual time=253.828..257.993 rows=1801 loops=1)',)
(' -> Hash Left Join (cost=8536.78..9826.56 rows=31 width=198) (actual time=253.821..256.281 rows=1801 loops=1)',)
(' Hash Cond: (("*SELECT* 1".tran_type = (due_120.tran_type)::text) AND ("*SELECT* 1".tran_row_id = due_120.tran_row_id))',)
(' Join Filter: ("*SELECT* 1".tran_date <= \'2015-05-31\'::date)',)
(' Rows Removed by Join Filter: 1190',)
(' -> Hash Right Join (cost=7798.13..9087.35 rows=31 width=228) (actual time=240.593..241.174 rows=1801 loops=1)',)
(' Hash Cond: (("*SELECT* 1_4".tran_type = (alloc_90.tran_type)::text) AND ("*SELECT* 1_4".tran_row_id = alloc_90.tran_row_id))',)
(' -> Append (cost=0.00..1019.01 rows=21603 width=36) (actual time=0.020..32.969 rows=21601 loops=1)',)
(' -> Subquery Scan on "*SELECT* 1_4" (cost=0.00..1000.03 rows=21601 width=36) (actual time=0.020..30.093 rows=21601 loops=1)',)
(' -> Seq Scan on ar_tran_inv ar_tran_inv_1 (cost=0.00..784.02 rows=21601 width=46) (actual time=0.019..25.634 rows=21601 loops=1)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date) AND (deleted_id = 0))",)
(' -> Subquery Scan on "*SELECT* 2_4" (cost=4.13..9.49 rows=1 width=36) (actual time=0.006..0.006 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_crn ar_tran_crn_1 (cost=4.13..9.48 rows=1 width=124) (actual time=0.005..0.005 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date))",)
(' -> Bitmap Index Scan on _ar_tran_crn (cost=0.00..4.13 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=1)',)
(' -> Subquery Scan on "*SELECT* 3_4" (cost=4.13..9.49 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_rec ar_tran_rec_1 (cost=4.13..9.48 rows=1 width=124) (actual time=0.009..0.009 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date))",)
(' -> Bitmap Index Scan on _ar_tran_rec (cost=0.00..4.13 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=1)',)
(' -> Hash (cost=7797.67..7797.67 rows=31 width=264) (actual time=203.557..203.557 rows=1801 loops=1)',)
(' Buckets: 1024 Batches: 1 Memory Usage: 84kB',)
(' -> Nested Loop Left Join (cost=6501.70..7797.67 rows=31 width=264) (actual time=198.899..202.729 rows=1801 loops=1)',)
(' -> Hash Left Join (cost=6501.55..7791.33 rows=31 width=192) (actual time=198.891..201.151 rows=1801 loops=1)',)
(' Hash Cond: (("*SELECT* 1".tran_type = (due_90.tran_type)::text) AND ("*SELECT* 1".tran_row_id = due_90.tran_row_id))',)
(' Join Filter: (("*SELECT* 1".tran_date > \'2015-05-31\'::date) AND ("*SELECT* 1".tran_date <= \'2015-06-30\'::date))',)
(' Rows Removed by Join Filter: 1501',)
(' -> Hash Right Join (cost=5762.90..7052.12 rows=31 width=182) (actual time=186.117..186.721 rows=1801 loops=1)',)
(' Hash Cond: (("*SELECT* 1_3".tran_type = (alloc_60.tran_type)::text) AND ("*SELECT* 1_3".tran_row_id = alloc_60.tran_row_id))',)
(' -> Append (cost=0.00..1019.01 rows=21603 width=36) (actual time=0.018..32.588 rows=21601 loops=1)',)
(' -> Subquery Scan on "*SELECT* 1_3" (cost=0.00..1000.03 rows=21601 width=36) (actual time=0.017..29.573 rows=21601 loops=1)',)
(' -> Seq Scan on ar_tran_inv ar_tran_inv_2 (cost=0.00..784.02 rows=21601 width=46) (actual time=0.016..25.241 rows=21601 loops=1)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date) AND (deleted_id = 0))",)
(' -> Subquery Scan on "*SELECT* 2_3" (cost=4.13..9.49 rows=1 width=36) (actual time=0.006..0.006 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_crn ar_tran_crn_2 (cost=4.13..9.48 rows=1 width=124) (actual time=0.006..0.006 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date))",)
(' -> Bitmap Index Scan on _ar_tran_crn (cost=0.00..4.13 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=1)',)
(' -> Subquery Scan on "*SELECT* 3_3" (cost=4.13..9.49 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_rec ar_tran_rec_2 (cost=4.13..9.48 rows=1 width=124) (actual time=0.008..0.008 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date))",)
(' -> Bitmap Index Scan on _ar_tran_rec (cost=0.00..4.13 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=1)',)
(' -> Hash (cost=5762.43..5762.43 rows=31 width=218) (actual time=149.507..149.507 rows=1801 loops=1)',)
(' Buckets: 1024 Batches: 1 Memory Usage: 82kB',)
(' -> Nested Loop Left Join (cost=4466.46..5762.43 rows=31 width=218) (actual time=144.985..148.644 rows=1801 loops=1)',)
(' -> Hash Left Join (cost=4466.31..5756.09 rows=31 width=146) (actual time=144.979..147.183 rows=1801 loops=1)',)
(' Hash Cond: (("*SELECT* 1".tran_type = (due_60.tran_type)::text) AND ("*SELECT* 1".tran_row_id = due_60.tran_row_id))',)
(' Join Filter: (("*SELECT* 1".tran_date > \'2015-06-30\'::date) AND ("*SELECT* 1".tran_date <= \'2015-07-31\'::date))',)
(' Rows Removed by Join Filter: 1491',)
(' -> Hash Right Join (cost=3727.66..5016.88 rows=31 width=136) (actual time=131.835..132.344 rows=1801 loops=1)',)
(' Hash Cond: (("*SELECT* 1_2".tran_type = (alloc_30.tran_type)::text) AND ("*SELECT* 1_2".tran_row_id = alloc_30.tran_row_id))',)
(' -> Append (cost=0.00..1019.01 rows=21603 width=36) (actual time=0.016..31.826 rows=21601 loops=1)',)
(' -> Subquery Scan on "*SELECT* 1_2" (cost=0.00..1000.03 rows=21601 width=36) (actual time=0.016..28.897 rows=21601 loops=1)',)
(' -> Seq Scan on ar_tran_inv ar_tran_inv_3 (cost=0.00..784.02 rows=21601 width=46) (actual time=0.015..24.692 rows=21601 loops=1)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date) AND (deleted_id = 0))",)
(' -> Subquery Scan on "*SELECT* 2_2" (cost=4.13..9.49 rows=1 width=36) (actual time=0.005..0.005 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_crn ar_tran_crn_3 (cost=4.13..9.48 rows=1 width=124) (actual time=0.003..0.003 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date))",)
(' -> Bitmap Index Scan on _ar_tran_crn (cost=0.00..4.13 rows=2 width=0) (actual time=0.003..0.003 rows=0 loops=1)',)
(' -> Subquery Scan on "*SELECT* 3_2" (cost=4.13..9.49 rows=1 width=36) (actual time=0.002..0.002 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_rec ar_tran_rec_3 (cost=4.13..9.48 rows=1 width=124) (actual time=0.002..0.002 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date))",)
(' -> Bitmap Index Scan on _ar_tran_rec (cost=0.00..4.13 rows=2 width=0) (actual time=0.001..0.001 rows=0 loops=1)',)
(' -> Hash (cost=3727.20..3727.20 rows=31 width=172) (actual time=95.950..95.950 rows=1801 loops=1)',)
(' Buckets: 1024 Batches: 1 Memory Usage: 81kB',)
(' -> Nested Loop Left Join (cost=2431.78..3727.20 rows=31 width=172) (actual time=93.367..95.221 rows=1801 loops=1)',)
(' -> Hash Right Join (cost=2431.64..3720.86 rows=31 width=100) (actual time=93.363..93.804 rows=1801 loops=1)',)
(' Hash Cond: (("*SELECT* 1_1".tran_type = (alloc_curr.tran_type)::text) AND ("*SELECT* 1_1".tran_row_id = alloc_curr.tran_row_id))',)
(' -> Append (cost=0.00..1019.01 rows=21603 width=36) (actual time=0.017..32.551 rows=21601 loops=1)',)
(' -> Subquery Scan on "*SELECT* 1_1" (cost=0.00..1000.03 rows=21601 width=36) (actual time=0.017..29.582 rows=21601 loops=1)',)
(' -> Seq Scan on ar_tran_inv ar_tran_inv_4 (cost=0.00..784.02 rows=21601 width=46) (actual time=0.015..25.205 rows=21601 loops=1)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date) AND (deleted_id = 0))",)
(' -> Subquery Scan on "*SELECT* 2_1" (cost=4.13..9.49 rows=1 width=36) (actual time=0.012..0.012 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_crn ar_tran_crn_4 (cost=4.13..9.48 rows=1 width=124) (actual time=0.012..0.012 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date))",)
(' -> Bitmap Index Scan on _ar_tran_crn (cost=0.00..4.13 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=1)',)
(' -> Subquery Scan on "*SELECT* 3_1" (cost=4.13..9.49 rows=1 width=36) (actual time=0.002..0.002 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_rec ar_tran_rec_4 (cost=4.13..9.48 rows=1 width=124) (actual time=0.002..0.002 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(" Filter: (posted AND (tran_date <= '2015-09-30'::date))",)
(' -> Bitmap Index Scan on _ar_tran_rec (cost=0.00..4.13 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=1)',)
(' -> Hash (cost=2431.17..2431.17 rows=31 width=136) (actual time=56.843..56.843 rows=1801 loops=1)',)
(' Buckets: 1024 Batches: 1 Memory Usage: 83kB',)
(' -> Nested Loop Left Join (cost=1740.28..2431.17 rows=31 width=136) (actual time=51.417..56.111 rows=1801 loops=1)',)
(' -> Hash Right Join (cost=1740.13..2424.83 rows=31 width=64) (actual time=51.410..54.688 rows=1801 loops=1)',)
(' Hash Cond: (((due_30.tran_type)::text = "*SELECT* 1".tran_type) AND (due_30.tran_row_id = "*SELECT* 1".tran_row_id))',)
(' Join Filter: (("*SELECT* 1".tran_date > \'2015-07-31\'::date) AND ("*SELECT* 1".tran_date <= \'2015-08-31\'::date))',)
(' Rows Removed by Join Filter: 1491',)
(' -> Seq Scan on ar_trans_due due_30 (cost=0.00..414.86 rows=21586 width=21) (actual time=0.004..2.725 rows=21601 loops=1)',)
(' -> Hash (cost=1739.67..1739.67 rows=31 width=54) (actual time=45.473..45.473 rows=1801 loops=1)',)
(' Buckets: 1024 Batches: 1 Memory Usage: 74kB',)
(' -> Hash Right Join (cost=1054.97..1739.67 rows=31 width=54) (actual time=43.069..44.786 rows=1801 loops=1)',)
(' Hash Cond: (((due_curr.tran_type)::text = "*SELECT* 1".tran_type) AND (due_curr.tran_row_id = "*SELECT* 1".tran_row_id))',)
(' Join Filter: ("*SELECT* 1".tran_date > \'2015-08-31\'::date)',)
(' Rows Removed by Join Filter: 1531',)
(' -> Seq Scan on ar_trans_due due_curr (cost=0.00..414.86 rows=21586 width=21) (actual time=0.008..3.239 rows=21601 loops=1)',)
(' -> Hash (cost=1054.50..1054.50 rows=31 width=44) (actual time=35.432..35.432 rows=1801 loops=1)',)
(' Buckets: 1024 Batches: 1 Memory Usage: 71kB',)
(' -> Hash Right Join (cost=8.18..1054.50 rows=31 width=44) (actual time=0.045..34.715 rows=1801 loops=1)',)
(' Hash Cond: ("*SELECT* 1".cust_row_id = a.row_id)',)
(' -> Append (cost=0.00..965.00 rows=21603 width=44) (actual time=0.017..31.134 rows=21601 loops=1)',)
(' -> Subquery Scan on "*SELECT* 1" (cost=0.00..946.03 rows=21601 width=44) (actual time=0.017..27.972 rows=21601 loops=1)',)
(' -> Seq Scan on ar_tran_inv ar_tran_inv_5 (cost=0.00..730.02 rows=21601 width=46) (actual time=0.017..23.557 rows=21601 loops=1)',)
(' Filter: (posted AND (deleted_id = 0))',)
(' -> Subquery Scan on "*SELECT* 2" (cost=4.13..9.49 rows=1 width=44) (actual time=0.009..0.009 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_crn ar_tran_crn_5 (cost=4.13..9.48 rows=1 width=124) (actual time=0.009..0.009 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(' Filter: posted',)
(' -> Bitmap Index Scan on _ar_tran_crn (cost=0.00..4.13 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=1)',)
(' -> Subquery Scan on "*SELECT* 3" (cost=4.13..9.49 rows=1 width=44) (actual time=0.003..0.003 rows=0 loops=1)',)
(' -> Bitmap Heap Scan on ar_tran_rec ar_tran_rec_5 (cost=4.13..9.48 rows=1 width=124) (actual time=0.002..0.002 rows=0 loops=1)',)
(' Recheck Cond: (deleted_id = 0)',)
(' Filter: posted',)
(' -> Bitmap Index Scan on _ar_tran_rec (cost=0.00..4.13 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=1)',)
(' -> Hash (cost=8.17..8.17 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)',)
(' Buckets: 1024 Batches: 1 Memory Usage: 1kB',)
(' -> Index Scan using _ar_customers on ar_customers a (cost=0.14..8.17 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)',)
(' Index Cond: ((ledger_row_id = 1) AND (party_row_id = 3))',)
(' -> Index Scan using ar_tr_alloc_ar_tr_due on ar_trans_alloc alloc_curr (cost=0.15..0.18 rows=2 width=80) (actual time=0.000..0.000 rows=0 loops=1801)',)
(' Index Cond: (due_row_id = due_curr.row_id)',)
(' -> Index Scan using ar_tr_alloc_ar_tr_due on ar_trans_alloc alloc_30 (cost=0.15..0.18 rows=2 width=80) (actual time=0.000..0.000 rows=0 loops=1801)',)
(' Index Cond: (due_row_id = due_30.row_id)',)
(' -> Hash (cost=414.86..414.86 rows=21586 width=21) (actual time=13.101..13.101 rows=21601 loops=1)',)
(' Buckets: 4096 Batches: 1 Memory Usage: 900kB',)
(' -> Seq Scan on ar_trans_due due_60 (cost=0.00..414.86 rows=21586 width=21) (actual time=0.005..5.308 rows=21601 loops=1)',)
(' -> Index Scan using ar_tr_alloc_ar_tr_due on ar_trans_alloc alloc_60 (cost=0.15..0.18 rows=2 width=80) (actual time=0.000..0.000 rows=0 loops=1801)',)
(' Index Cond: (due_row_id = due_60.row_id)',)
(' -> Hash (cost=414.86..414.86 rows=21586 width=21) (actual time=12.734..12.734 rows=21601 loops=1)',)
(' Buckets: 4096 Batches: 1 Memory Usage: 900kB',)
(' -> Seq Scan on ar_trans_due due_90 (cost=0.00..414.86 rows=21586 width=21) (actual time=0.005..5.219 rows=21601 loops=1)',)
(' -> Index Scan using ar_tr_alloc_ar_tr_due on ar_trans_alloc alloc_90 (cost=0.15..0.18 rows=2 width=80) (actual time=0.000..0.000 rows=0 loops=1801)',)
(' Index Cond: (due_row_id = due_90.row_id)',)
(' -> Hash (cost=414.86..414.86 rows=21586 width=21) (actual time=13.186..13.186 rows=21601 loops=1)',)
(' Buckets: 4096 Batches: 1 Memory Usage: 900kB',)
(' -> Seq Scan on ar_trans_due due_120 (cost=0.00..414.86 rows=21586 width=21) (actual time=0.005..5.280 rows=21601 loops=1)',)
(' -> Index Scan using ar_tr_alloc_ar_tr_due on ar_trans_alloc alloc_120 (cost=0.15..0.18 rows=2 width=80) (actual time=0.000..0.000 rows=0 loops=1801)',)
(' Index Cond: (due_row_id = due_120.row_id)',)
(' SubPlan 1',)
(' -> Limit (cost=87.29..87.30 rows=1 width=38) (actual time=1.194..1.194 rows=1 loops=1)',)
(' -> Sort (cost=87.29..87.74 rows=179 width=38) (actual time=1.193..1.193 rows=1 loops=1)',)
(' Sort Key: b.tran_date',)
(' Sort Method: top-N heapsort Memory: 17kB',)
(' -> Seq Scan on ar_cust_totals b (cost=0.00..86.40 rows=179 width=38) (actual time=0.020..0.966 rows=180 loops=1)',)
(" Filter: ((tran_date <= '2015-09-30'::date) AND (cust_row_id = a.row_id))",)
(' Rows Removed by Filter: 1980',)
('Planning time: 7.918 ms',)
('Execution time: 300.892 ms',)
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
