explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AaEC

Settings
# exclusive inclusive rows x rows loops node
1. 0.064 12,320.890 ↑ 1.0 100 1

Limit (cost=4,083,683.45..4,083,869.18 rows=100 width=476) (actual time=12,308.209..12,320.890 rows=100 loops=1)

2.          

CTE main

3. 6.447 12,313.059 ↑ 1,392.5 100 1

GroupAggregate (cost=4,031,460.62..4,083,678.41 rows=139,248 width=43) (actual time=12,303.988..12,313.059 rows=100 loops=1)

  • Group Key: analytics_data.partnera_strukturvienibas_id, analytics_data.partneris_id, analytics_data.preces_id, analytics_data.nodoklis, (date_trunc('month'::text, (analytics_data.datums)::timestamp with time zone)), (NULL::integer)
4. 7,850.708 12,306.612 ↑ 270.0 5,158 1

Sort (cost=4,031,460.62..4,034,941.80 rows=1,392,474 width=43) (actual time=12,303.439..12,306.612 rows=5,158 loops=1)

  • Sort Key: analytics_data.partnera_strukturvienibas_id, analytics_data.partneris_id, analytics_data.preces_id, analytics_data.nodoklis, (date_trunc('month'::text, (analytics_data.datums)::timestamp with time zone)), (NULL::integer)
  • Sort Method: external merge Disk: 264304kB
5. 4,086.757 4,455.904 ↓ 3.0 4,174,547 1

Bitmap Heap Scan on analytics_data (cost=35,359.36..3,846,527.58 rows=1,392,474 width=43) (actual time=376.787..4,455.904 rows=4,174,547 loops=1)

  • Recheck Cond: ((datums >= '2018-03-01'::date) AND (datums <= '2018-03-31'::date))
  • Rows Removed by Index Recheck: 1079655
  • Filter: (pz_tips = ANY ('{207,200}'::integer[]))
  • Rows Removed by Filter: 575347
  • Heap Blocks: exact=42905 lossy=107358
6. 369.147 369.147 ↓ 3.0 4,749,894 1

Bitmap Index Scan on analytics_data_datums_idx (cost=0.00..35,011.24 rows=1,566,267 width=0) (actual time=369.147..369.147 rows=4,749,894 loops=1)

  • Index Cond: ((datums >= '2018-03-01'::date) AND (datums <= '2018-03-31'::date))
7. 0.110 12,320.826 ↑ 1,392.5 100 1

Nested Loop Left Join (cost=5.04..258,626.20 rows=139,248 width=476) (actual time=12,308.208..12,320.826 rows=100 loops=1)

8. 0.118 12,319.116 ↑ 1,392.5 100 1

Nested Loop Left Join (cost=4.77..215,643.50 rows=139,248 width=460) (actual time=12,308.192..12,319.116 rows=100 loops=1)

9. 0.141 12,317.198 ↑ 1,392.5 100 1

Nested Loop Left Join (cost=4.63..186,414.95 rows=139,248 width=246) (actual time=12,306.554..12,317.198 rows=100 loops=1)

10. 0.198 12,316.857 ↑ 1,392.5 100 1

Nested Loop Left Join (cost=4.48..161,968.17 rows=139,248 width=218) (actual time=12,306.539..12,316.857 rows=100 loops=1)

11. 0.186 12,315.359 ↑ 1,392.5 100 1

Nested Loop Left Join (cost=4.06..90,710.57 rows=139,248 width=209) (actual time=12,305.511..12,315.359 rows=100 loops=1)

12. 0.094 12,313.473 ↑ 1,392.5 100 1

Hash Left Join (cost=3.64..4,341.21 rows=139,248 width=172) (actual time=12,304.094..12,313.473 rows=100 loops=1)

  • Hash Cond: (main.partnera_strukturvienibas_id = asv.id)
13. 0.116 12,313.321 ↑ 1,392.5 100 1

Hash Left Join (cost=1.04..3,322.11 rows=139,248 width=163) (actual time=12,304.031..12,313.321 rows=100 loops=1)

  • Hash Cond: (main.partneris_id = ap.id)
14. 12,313.178 12,313.178 ↑ 1,392.5 100 1

CTE Scan on main (cost=0.00..2,784.96 rows=139,248 width=156) (actual time=12,303.991..12,313.178 rows=100 loops=1)

15. 0.002 0.027 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=15) (actual time=0.027..0.027 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.025 0.025 ↑ 1.0 2 1

Seq Scan on analytics_partneri ap (cost=0.00..1.02 rows=2 width=15) (actual time=0.023..0.025 rows=2 loops=1)

17. 0.028 0.058 ↑ 1.0 71 1

Hash (cost=1.71..1.71 rows=71 width=17) (actual time=0.058..0.058 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
18. 0.030 0.030 ↑ 1.0 71 1

Seq Scan on analytics_strukturvienibas asv (cost=0.00..1.71 rows=71 width=17) (actual time=0.007..0.030 rows=71 loops=1)

19. 1.700 1.700 ↑ 1.0 1 100

Index Scan using precesid on preces p (cost=0.42..0.61 rows=1 width=41) (actual time=0.017..0.017 rows=1 loops=100)

  • Index Cond: (id = main.preces_id)
20. 1.300 1.300 ↑ 1.0 1 100

Index Scan using artikuli_1i on artikuli ean (cost=0.42..0.50 rows=1 width=17) (actual time=0.013..0.013 rows=1 loops=100)

  • Index Cond: ((preces_id = main.preces_id) AND (artikulu_veidi_id = 1))
  • Filter: (NOT disabled)
  • Rows Removed by Filter: 19
21. 0.200 0.200 ↑ 1.0 1 100

Index Scan using precu_grupas_pkey on precu_grupas pg (cost=0.15..0.17 rows=1 width=36) (actual time=0.001..0.002 rows=1 loops=100)

  • Index Cond: (p.grupas_id = id)
22. 1.800 1.800 ↑ 1.0 1 100

Index Scan using kategorijas_pkey on kategorijas pgk (cost=0.14..0.20 rows=1 width=222) (actual time=0.017..0.018 rows=1 loops=100)

  • Index Cond: (pg.kategorijas_id = id)
23. 1.600 1.600 ↑ 1.0 1 100

Index Scan using klienti_pkey on klienti k (cost=0.28..0.30 rows=1 width=24) (actual time=0.015..0.016 rows=1 loops=100)

  • Index Cond: (p.pieg_id = id)