explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gfs3 : Optimization for: plan #za6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.086 884,353.710 ↓ 1.4 272 1

Sort (cost=7,378,176.43..7,378,176.93 rows=200 width=8) (actual time=884,353.692..884,353.710 rows=272 loops=1)

  • Sort Key: (count(*))
  • Sort Method: quicksort Memory: 37kB
2.          

CTE yy

3. 0.000 0.000 ↓ 0.0 0

Seq Scan on fp_res_2015_4 fp_res (cost=0.00..84,004.49 rows=2,096,716 width=36) (never executed)

  • Filter: ((fp_date >= '2015-04-06 00:00:00'::timestamp without time zone) AND (fp_date < '2015-04-26 23:59:00'::timestamp without time zone))
4.          

CTE xx

5. 12,634.367 855,436.576 ↑ 1.0 14,950,751 1

WindowAgg (cost=5,909,918.58..6,283,956.51 rows=14,961,517 width=24) (actual time=821,755.280..855,436.576 rows=14,950,751 loops=1)

6. 58,190.989 842,802.209 ↑ 1.0 14,950,751 1

Sort (cost=5,909,918.58..5,947,322.37 rows=14,961,517 width=24) (actual time=821,755.261..842,802.209 rows=14,950,751 loops=1)

  • Sort Key: fp_smp_ext.fp_device_id, fp_smp_ext.fp_date
  • Sort Method: external merge Disk: 496880kB
7. 784,611.220 784,611.220 ↑ 1.0 14,950,751 1

Seq Scan on fp_smp_2015_4 fp_smp_ext (cost=0.00..3,820,067.26 rows=14,961,517 width=24) (actual time=742.894..784,611.220 rows=14,950,751 loops=1)

  • Filter: ((fp_date >= '2015-04-05 23:00:00'::timestamp without time zone) AND (fp_date < '2015-04-27 00:59:00'::timestamp without time zone))
  • Rows Removed by Filter: 6190733
8.          

CTE x

9. 6,884.497 871,587.919 ↑ 1.0 14,886,214 1

Hash Join (cost=303.38..636,167.86 rows=14,961,517 width=40) (actual time=821,758.530..871,587.919 rows=14,886,214 loops=1)

  • Hash Cond: (xx.fp_device_id = fp_device.id)
10. 864,700.191 864,700.191 ↑ 1.0 14,950,751 1

CTE Scan on xx (cost=0.00..299,230.34 rows=14,961,517 width=40) (actual time=821,755.282..864,700.191 rows=14,950,751 loops=1)

11. 1.645 3.231 ↑ 1.0 8,506 1

Hash (cost=197.06..197.06 rows=8,506 width=16) (actual time=3.231..3.231 rows=8,506 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 399kB
12. 1.586 1.586 ↑ 1.0 8,506 1

Seq Scan on fp_device (cost=0.00..197.06 rows=8,506 width=16) (actual time=0.011..1.586 rows=8,506 loops=1)

13. 4,755.855 884,353.624 ↓ 1.4 272 1

HashAggregate (cost=374,037.92..374,039.92 rows=200 width=8) (actual time=884,353.571..884,353.624 rows=272 loops=1)

  • Group Key: x.fp_device_id
14. 879,597.769 879,597.769 ↑ 1.0 14,886,214 1

CTE Scan on x (cost=0.00..299,230.34 rows=14,961,517 width=8) (actual time=821,758.533..879,597.769 rows=14,886,214 loops=1)

Planning time : 0.347 ms
Execution time : 884,767.275 ms