explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gUtH : Ric-XANA

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 569.728 1,254,088.368 ↑ 1.7 291,299 1

Hash Join (cost=15,848,628.65..15,888,142.62 rows=486,326 width=141) (actual time=1,252,365.550..1,254,088.368 rows=291,299 loops=1)

  • Hash Cond: (the_view.geocerca_id = geocerca.geocerca_id)
2.          

CTE the_view

3. 6,715.768 1,252,756.222 ↑ 3.9 371,430 1

Sort (cost=15,844,851.43..15,848,498.87 rows=1,458,977 width=116) (actual time=1,252,362.283..1,252,756.222 rows=371,430 loops=1)

  • Sort Key: the_data_o.serial_num, the_data_o.source_prov, the_data_o.device_id, (min(the_data_o.visit_datetime))
  • Sort Method: external sort Disk: 29760kB
4.          

CTE the_data_o

5. 42,271.045 1,113,352.840 ↓ 1.1 15,754,855 1

WindowAgg (cost=9,168,484.46..10,408,614.57 rows=14,589,766 width=124) (actual time=910,599.213..1,113,352.840 rows=15,754,855 loops=1)

6.          

CTE the_data

7. 40,630.132 570,127.482 ↓ 1.1 15,754,855 1

WindowAgg (cost=4,046,560.74..4,447,779.31 rows=14,589,766 width=45) (actual time=451,382.467..570,127.482 rows=15,754,855 loops=1)

8. 400,132.707 529,497.350 ↓ 1.1 15,754,855 1

Sort (cost=4,046,560.74..4,083,035.16 rows=14,589,766 width=45) (actual time=451,382.335..529,497.350 rows=15,754,855 loops=1)

  • Sort Key: trans_tc_last_pstn_geocerca.serial_num, trans_tc_last_pstn_geocerca.source_prov, trans_tc_last_pstn_geocerca.device_id, trans_tc_last_pstn_geocerca.visit_datetime, trans_tc_last_pstn_geocerca.pstn_id
  • Sort Method: external merge Disk: 954416kB
9. 62,095.496 129,364.643 ↓ 1.1 15,754,855 1

Bitmap Heap Scan on trans_tc_last_pstn_geocerca (cost=330,577.67..1,412,874.41 rows=14,589,766 width=45) (actual time=67,282.586..129,364.643 rows=15,754,855 loops=1)

  • Recheck Cond: ((visit_datetime >= (now() - '24:00:00'::interval)) AND (visit_datetime <= now()))
  • Rows Removed by Index Recheck: 104124
  • Heap Blocks: exact=38023 lossy=158112
10. 67,269.147 67,269.147 ↓ 1.1 15,754,855 1

Bitmap Index Scan on trans_tc_last_pstn_geocerca_n1 (cost=0.00..326,930.23 rows=14,589,766 width=0) (actual time=67,269.147..67,269.147 rows=15,754,855 loops=1)

  • Index Cond: ((visit_datetime >= (now() - '24:00:00'::interval)) AND (visit_datetime <= now()))
11. 27,875.910 1,071,081.795 ↓ 1.1 15,754,855 1

Subquery Scan on s (cost=4,720,705.15..5,596,091.11 rows=14,589,766 width=124) (actual time=910,597.879..1,071,081.795 rows=15,754,855 loops=1)

12. 51,485.353 1,043,205.885 ↓ 1.1 15,754,855 1

WindowAgg (cost=4,720,705.15..5,450,193.45 rows=14,589,766 width=120) (actual time=910,597.876..1,043,205.885 rows=15,754,855 loops=1)

13. 382,180.999 991,720.532 ↓ 1.1 15,754,855 1

Sort (cost=4,720,705.15..4,757,179.57 rows=14,589,766 width=120) (actual time=910,597.825..991,720.532 rows=15,754,855 loops=1)

  • Sort Key: the_data.serial_num, the_data.source_prov, the_data.device_id, the_data.geocerca_id, the_data.gps_geo_hist
  • Sort Method: external merge Disk: 1139088kB
14. 609,539.533 609,539.533 ↓ 1.1 15,754,855 1

CTE Scan on the_data (cost=0.00..291,795.32 rows=14,589,766 width=120) (actual time=451,382.475..609,539.533 rows=15,754,855 loops=1)

15. 20,249.078 1,246,040.454 ↑ 3.9 371,430 1

GroupAggregate (cost=4,720,705.15..5,107,333.96 rows=1,458,977 width=116) (actual time=1,208,245.866..1,246,040.454 rows=371,430 loops=1)

  • Group Key: the_data_o.gps_geo_hist_group, the_data_o.geocerca_id, the_data_o.serial_num, the_data_o.source_prov, the_data_o.device_id
16. 72,417.712 1,225,791.376 ↓ 1.1 15,754,855 1

Sort (cost=4,720,705.15..4,757,179.57 rows=14,589,766 width=116) (actual time=1,208,245.793..1,225,791.376 rows=15,754,855 loops=1)

  • Sort Key: the_data_o.gps_geo_hist_group, the_data_o.geocerca_id, the_data_o.serial_num, the_data_o.source_prov, the_data_o.device_id
  • Sort Method: external sort Disk: 1015952kB
17. 1,153,373.664 1,153,373.664 ↓ 1.1 15,754,855 1

CTE Scan on the_data_o (cost=0.00..291,795.32 rows=14,589,766 width=116) (actual time=910,599.221..1,153,373.664 rows=15,754,855 loops=1)

18. 1,253,515.490 1,253,515.490 ↑ 1.7 291,299 1

CTE Scan on the_view (cost=0.00..32,826.98 rows=486,326 width=124) (actual time=1,252,362.299..1,253,515.490 rows=291,299 loops=1)

  • Filter: (timespend > '0'::double precision)
  • Rows Removed by Filter: 80131
19. 1.405 3.150 ↑ 1.2 923 1

Hash (cost=115.46..115.46 rows=1,146 width=21) (actual time=3.150..3.150 rows=923 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 66kB
20. 1.745 1.745 ↑ 1.2 923 1

Seq Scan on cat_tc_geocerca geocerca (cost=0.00..115.46 rows=1,146 width=21) (actual time=0.071..1.745 rows=923 loops=1)

Planning time : 0.708 ms
Execution time : 1,254,805.694 ms