explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gKKW

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 21,525.380 ↓ 0.0 0 1

GroupAggregate (cost=398.71..471,978.67 rows=1 width=20) (actual time=21,525.380..21,525.380 rows=0 loops=1)

  • Group Key: kva.userid, kva.actualvisitdate
2. 0.003 21,525.378 ↓ 0.0 0 1

Nested Loop (cost=398.71..471,978.65 rows=1 width=20) (actual time=21,525.378..21,525.378 rows=0 loops=1)

  • Join Filter: ("*SELECT* 1".saleareaid = saleareaid.orgstructid)
3. 0.010 21,525.375 ↓ 0.0 0 1

Nested Loop (cost=398.71..470,672.43 rows=1 width=28) (actual time=21,525.375..21,525.375 rows=0 loops=1)

4. 0.008 21,509.153 ↓ 2.0 2 1

Nested Loop (cost=398.71..469,366.23 rows=1 width=28) (actual time=5.167..21,509.153 rows=2 loops=1)

5. 0.067 0.067 ↓ 2.0 2 1

Index Scan using idx_kx_visit_actual_actualvisitdate on kx_visit_actual kva (cost=0.43..8.46 rows=1 width=28) (actual time=0.062..0.067 rows=2 loops=1)

  • Index Cond: ((actualvisitdate >= '2019-04-13'::date) AND (actualvisitdate <= '2019-04-13'::date))
  • Filter: ((platstatus = 1) AND (userid = '1115549561616732160'::bigint) AND ((visittype)::text = '2'::text))
6. 0.028 21,509.078 ↑ 3.0 1 2

Append (cost=398.28..469,357.73 rows=3 width=16) (actual time=3,001.181..10,754.539 rows=1 loops=2)

7. 225.652 21,466.728 ↑ 1.0 1 2

Subquery Scan on *SELECT* 1 (cost=398.28..467,672.54 rows=1 width=16) (actual time=3,001.178..10,733.364 rows=1 loops=2)

  • Filter: (kva.customerid = "*SELECT* 1".id)
  • Rows Removed by Filter: 899999
8. 18,029.599 21,241.076 ↑ 1.0 900,000 2

Hash Left Join (cost=398.28..456,422.54 rows=900,000 width=2,002) (actual time=2.564..10,620.538 rows=900,000 loops=2)

  • Hash Cond: (ks.storelevel = pd3.dickey)
9. 595.955 3,211.352 ↑ 1.0 900,000 2

Hash Left Join (cost=380.36..219,029.62 rows=900,000 width=112) (actual time=2.063..1,605.676 rows=900,000 loops=2)

  • Hash Cond: (ks.storetype = pd2.dickey)
10. 602.228 2,615.256 ↑ 1.0 900,000 2

Hash Left Join (cost=362.44..206,636.70 rows=900,000 width=120) (actual time=1.985..1,307.628 rows=900,000 loops=2)

  • Hash Cond: (ks.channeltype = pd1.dickey)
11. 796.591 2,012.840 ↑ 1.0 900,000 2

Hash Left Join (cost=344.52..194,243.78 rows=900,000 width=128) (actual time=1.879..1,006.420 rows=900,000 loops=2)

  • Hash Cond: (ks.seleareaid = ps.orgstructid)
12. 1,212.560 1,212.560 ↑ 1.0 900,000 2

Seq Scan on kx_kq_store ks (cost=0.00..191,535.00 rows=900,000 width=40) (actual time=0.007..606.280 rows=900,000 loops=2)

  • Filter: (platstatus = 1)
13. 1.161 3.689 ↓ 1.0 5,718 1

Hash (cost=273.12..273.12 rows=5,712 width=96) (actual time=3.689..3.689 rows=5,718 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 339kB
14. 2.528 2.528 ↓ 1.0 5,718 1

Seq Scan on pl_orgstruct_xn ps (cost=0.00..273.12 rows=5,712 width=96) (actual time=0.010..2.528 rows=5,718 loops=1)

15. 0.069 0.188 ↑ 1.0 352 1

Hash (cost=13.52..13.52 rows=352 width=8) (actual time=0.187..0.188 rows=352 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
16. 0.119 0.119 ↑ 1.0 352 1

Seq Scan on pl_dictionary pd1 (cost=0.00..13.52 rows=352 width=8) (actual time=0.012..0.119 rows=352 loops=1)

17. 0.079 0.141 ↑ 1.0 352 1

Hash (cost=13.52..13.52 rows=352 width=8) (actual time=0.141..0.141 rows=352 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
18. 0.062 0.062 ↑ 1.0 352 1

Seq Scan on pl_dictionary pd2 (cost=0.00..13.52 rows=352 width=8) (actual time=0.002..0.062 rows=352 loops=1)

19. 0.065 0.125 ↑ 1.0 352 1

Hash (cost=13.52..13.52 rows=352 width=8) (actual time=0.125..0.125 rows=352 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
20. 0.060 0.060 ↑ 1.0 352 1

Seq Scan on pl_dictionary pd3 (cost=0.00..13.52 rows=352 width=8) (actual time=0.001..0.060 rows=352 loops=1)

21. 0.600 42.310 ↓ 0.0 0 2

Subquery Scan on *SELECT* 2 (cost=927.15..1,677.04 rows=1 width=16) (actual time=21.155..21.155 rows=0 loops=2)

  • Filter: (kva.customerid = "*SELECT* 2".id)
  • Rows Removed by Filter: 2521
22. 34.680 41.710 ↑ 1.0 2,521 2

Hash Left Join (cost=927.15..1,645.53 rows=2,521 width=2,002) (actual time=2.412..20.855 rows=2,521 loops=2)

  • Hash Cond: (kc.channeltype = pd1_1.dickey)
23. 1.510 6.882 ↑ 1.0 2,521 2

Merge Right Join (cost=909.23..962.69 rows=2,521 width=112) (actual time=2.311..3.441 rows=2,521 loops=2)

  • Merge Cond: (ps_1.orgstructid = kc.saleareaid)
24. 0.174 0.174 ↑ 24.9 229 2

Index Scan using ix_pl_orgstruct_orgstructid on pl_orgstruct_xn ps_1 (cost=0.28..378.04 rows=5,712 width=96) (actual time=0.017..0.087 rows=229 loops=2)

25. 1.397 5.198 ↑ 1.0 2,521 2

Sort (cost=908.95..915.25 rows=2,521 width=24) (actual time=2.288..2.599 rows=2,521 loops=2)

  • Sort Key: kc.saleareaid
  • Sort Method: quicksort Memory: 293kB
26. 3.801 3.801 ↑ 1.0 2,521 1

Seq Scan on ka_kq_channelcustomers kc (cost=0.00..766.51 rows=2,521 width=24) (actual time=0.014..3.801 rows=2,521 loops=1)

  • Filter: (platstatus = 1)
27. 0.059 0.148 ↑ 1.0 352 1

Hash (cost=13.52..13.52 rows=352 width=8) (actual time=0.147..0.148 rows=352 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
28. 0.089 0.089 ↑ 1.0 352 1

Seq Scan on pl_dictionary pd1_1 (cost=0.00..13.52 rows=352 width=8) (actual time=0.006..0.089 rows=352 loops=1)

29. 0.012 0.012 ↓ 0.0 0 2

Index Scan using kx_kq_ka_pkey on kx_kq_ka ka (cost=0.14..8.16 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=2)

  • Index Cond: (id = kva.customerid)
  • Filter: (platstatus = 1)
30. 16.212 16.212 ↓ 0.0 0 2

Seq Scan on pl_orgstruct userid (cost=0.00..1,306.19 rows=1 width=8) (actual time=8.106..8.106 rows=0 loops=2)

  • Filter: ((platstatus = 1) AND (orgstructid = '1115549561616732160'::bigint))
  • Rows Removed by Filter: 22259
31. 0.000 0.000 ↓ 0.0 0

Seq Scan on pl_orgstruct saleareaid (cost=0.00..1,306.19 rows=2 width=8) (never executed)

  • Filter: (((codepath)::text ~~ '1.1115519458308395008.1115519459885453312.1115523005775220736.1115523051472162816.%'::text) AND (platstatus = 1))