Pavel Stehule wrote: > > 2017-09-12 8:45 GMT+02:00 Frank Millman <fr...@chagford.com>:
I am using 9.4.4 on Fedora 22. I am experimenting with optimising a SQL statement. One version uses 4 LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements. My test involves selecting a single row. Both versions work. The first version takes 0.06 seconds. The second takes 0.23 seconds. On further experimentation, the time for the second one seems to taken in setting up the joins, because if I omit selecting anything from the joined tables, it still takes 0.23 seconds. > > 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general