explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6jbo : Optimization for: plan #3dVC

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.594 106.472 ↑ 100.6 175 1

HashAggregate (cost=129,336.90..129,908.81 rows=17,597 width=136) (actual time=106.140..106.472 rows=175 loops=1)

  • Group Key: cp.cartodb_id
2.          

CTE _transacts

3. 2.671 26.482 ↑ 5.3 1,045 1

HashAggregate (cost=25,860.72..25,930.50 rows=5,582 width=73) (actual time=25.903..26.482 rows=1,045 loops=1)

  • Group Key: akto_transacciones_clientes_particulares.id_socio
4. 22.246 23.811 ↑ 2.1 2,700 1

Bitmap Heap Scan on akto_transacciones_clientes_particulares (cost=189.98..25,832.57 rows=5,630 width=49) (actual time=2.954..23.811 rows=2,700 loops=1)

  • Recheck Cond: (n_concesion = 19268)
  • Filter: ((fecha >= '2019-01-01'::date) AND (fecha <= '2019-12-31'::date) AND (descripcion = 'GASOLEO A'::text))
  • Rows Removed by Filter: 6467
  • Heap Blocks: exact=8373
5. 1.565 1.565 ↑ 1.1 9,167 1

Bitmap Index Scan on _trans_cpart_n_conces_idx (cost=0.00..188.57 rows=10,152 width=0) (actual time=1.565..1.565 rows=9,167 loops=1)

  • Index Cond: (n_concesion = 19268)
6.          

CTE _aux

7. 0.227 104.039 ↑ 95.7 175 1

Hash Join (cost=219.15..930.85 rows=16,746 width=64) (actual time=103.642..104.039 rows=175 loops=1)

  • Hash Cond: (t.id_socio = (unnest(e.id_socios)))
8. 26.113 26.113 ↑ 5.3 1,045 1

CTE Scan on _transacts t (cost=0.00..111.64 rows=5,582 width=64) (actual time=25.908..26.113 rows=1,045 loops=1)

9. 0.065 77.699 ↑ 3.4 175 1

Hash (cost=211.65..211.65 rows=600 width=32) (actual time=77.699..77.699 rows=175 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
10. 0.741 77.634 ↑ 3.4 175 1

ProjectSet (cost=178.54..205.65 rows=600 width=32) (actual time=67.236..77.634 rows=175 loops=1)

11. 0.044 76.893 ↓ 29.2 175 1

Nested Loop (cost=178.54..202.60 rows=6 width=32) (actual time=67.218..76.893 rows=175 loops=1)

12. 0.249 1.199 ↑ 1.0 1 1

Aggregate (cost=125.60..125.61 rows=1 width=32) (actual time=1.198..1.199 rows=1 loops=1)

13. 0.950 0.950 ↑ 5.3 1,045 1

CTE Scan on _transacts (cost=0.00..111.64 rows=5,582 width=32) (actual time=0.001..0.950 rows=1,045 loops=1)

14. 9.839 75.650 ↓ 29.2 175 1

Bitmap Heap Scan on akto_estaciones_clientes_particulares e (cost=52.94..76.92 rows=6 width=32) (actual time=66.004..75.650 rows=175 loops=1)

  • Recheck Cond: ((n_concesion = '19268'::numeric) AND (id_socios <@ (array_agg(_transacts.id_socio))))
  • Rows Removed by Index Recheck: 138
  • Filter: (distance < '50'::numeric)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=313
15. 0.136 65.811 ↓ 0.0 0 1

BitmapAnd (cost=52.94..52.94 rows=6 width=0) (actual time=65.811..65.811 rows=0 loops=1)

16. 0.174 0.174 ↑ 1.3 851 1

Bitmap Index Scan on akto_estaciones_clientes_part_cod_est_idx (cost=0.00..25.01 rows=1,145 width=0) (actual time=0.174..0.174 rows=851 loops=1)

  • Index Cond: (n_concesion = '19268'::numeric)
17. 65.501 65.501 ↑ 1.4 681 1

Bitmap Index Scan on akto_estaciones_clientes_part_id_soc_idx (cost=0.00..27.40 rows=987 width=0) (actual time=65.501..65.501 rows=681 loops=1)

  • Index Cond: (id_socios <@ (array_agg(_transacts.id_socio)))
18. 0.020 105.878 ↑ 100.6 175 1

Nested Loop (cost=0.55..102,211.60 rows=17,597 width=104) (actual time=103.677..105.878 rows=175 loops=1)

19. 104.108 104.108 ↑ 95.7 175 1

CTE Scan on _aux a (cost=0.00..334.92 rows=16,746 width=64) (actual time=103.645..104.108 rows=175 loops=1)

20. 1.750 1.750 ↑ 1.0 1 175

Index Scan using akto_clientes_part_id_socio_idx on akto_clientes_particulares cp (cost=0.55..6.07 rows=1 width=113) (actual time=0.009..0.010 rows=1 loops=175)

  • Index Cond: (id_socio = a.id_socio)
Planning time : 0.640 ms
Execution time : 107.346 ms