Pavel Stehule wrote:
>
> 2017-09-12 8:45 GMT+02:00 Frank Millman <[email protected]>:
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
