explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4zWA : Optimization for: qlbz; plan #pZ57

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.024 41,221.902 ↓ 10.0 20 1

Limit (cost=795.21..795.21 rows=2 width=79) (actual time=41,221.869..41,221.902 rows=20 loops=1)

2. 2.811 41,221.878 ↓ 10.0 20 1

Sort (cost=795.21..795.21 rows=2 width=79) (actual time=41,221.867..41,221.878 rows=20 loops=1)

  • Sort Key: (((((((((h.doctype)::text || '\'::text) || (h.number)::text) || '\'::text) || (date_part('year'::text, (h.dateofissue)::timestamp without time zone))::text) || '\R1\'::text) || (h.magazine)::text))::character varying)
  • Sort Method: top-N heapsort Memory: 30kB
3. 41,117.297 41,219.067 ↓ 362.5 725 1

Nested Loop Left Join (cost=757.57..795.20 rows=2 width=79) (actual time=133.705..41,219.067 rows=725 loops=1)

4. 3.528 91.620 ↓ 362.5 725 1

Nested Loop Left Join (cost=757.57..793.67 rows=2 width=68) (actual time=72.879..91.620 rows=725 loops=1)

5. 14.389 82.292 ↓ 362.5 725 1

Merge Join (cost=757.57..793.56 rows=2 width=38) (actual time=72.868..82.292 rows=725 loops=1)

  • Merge Cond: ((u.cnid = cn.cnid) AND (u.cid = cn.cid) AND ((COALESCE((h.bid)::integer, 0)) = (COALESCE((cn.bid)::integer, 0))))
6. 10.644 37.659 ↓ 14.3 5,619 1

Sort (cost=449.22..449.42 rows=394 width=58) (actual time=34.136..37.659 rows=5,619 loops=1)

  • Sort Key: u.cnid, u.cid, (COALESCE((h.bid)::integer, 0))
  • Sort Method: quicksort Memory: 983kB
7. 7.212 27.015 ↓ 14.3 5,619 1

Nested Loop (cost=13.99..445.83 rows=394 width=58) (actual time=2.767..27.015 rows=5,619 loops=1)

8. 0.018 0.018 ↑ 1.0 1 1

Index Scan using zoneusers_idx on zoneusers u (cost=0.00..8.05 rows=1 width=10) (actual time=0.016..0.018 rows=1 loops=1)

  • Index Cond: ((zonelogin)::text = 'JANWITOSA'::text)
9. 17.806 19.785 ↓ 52.0 5,619 1

Bitmap Heap Scan on saleshdr h (cost=13.99..437.50 rows=108 width=48) (actual time=2.747..19.785 rows=5,619 loops=1)

  • Recheck Cond: ((cid = u.cid) AND (dateofissue >= '2019-01-01'::date) AND (dateofissue <= '2020-01-14'::date))
  • Filter: (status = ANY ('{3,4,5}'::integer[]))
10. 1.979 1.979 ↓ 53.4 5,771 1

Bitmap Index Scan on saleshdr_bonus_idx3 (cost=0.00..13.98 rows=108 width=0) (actual time=1.979..1.979 rows=5,771 loops=1)

  • Index Cond: ((cid = u.cid) AND (dateofissue >= '2019-01-01'::date) AND (dateofissue <= '2020-01-14'::date))
11. 21.680 30.244 ↑ 1.0 11,262 1

Sort (cost=308.35..314.16 rows=11,631 width=12) (actual time=23.260..30.244 rows=11,262 loops=1)

  • Sort Key: cn.cnid, cn.cid, (COALESCE((cn.bid)::integer, 0))
  • Sort Method: quicksort Memory: 922kB
12. 8.564 8.564 ↑ 1.0 11,475 1

Seq Scan on contactnames cn (cost=0.00..151.26 rows=11,631 width=12) (actual time=0.009..8.564 rows=11,475 loops=1)

13. 5.800 5.800 ↑ 1.0 1 725

Index Scan using statuses_pk on statuses s (cost=0.00..0.05 rows=1 width=34) (actual time=0.006..0.008 rows=1 loops=725)

  • Index Cond: (ids = h.status)
14. 10.150 10.150 ↑ 1.0 1 725

Index Scan using associationsaggregate_sales_idx1 on associationsaggregate a (cost=0.00..0.71 rows=1 width=19) (actual time=0.011..0.014 rows=1 loops=725)

  • Index Cond: (sid = h.sid)
Total runtime : 41,222.112 ms