explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pxwr

Settings
# exclusive inclusive rows x rows loops node
1. 223.892 12,439.012 ↑ 1.8 212,422 1

Hash Join (cost=17,887,217.820..19,701,915.340 rows=381,239 width=132) (actual time=12,148.532..12,439.012 rows=212,422 loops=1)

  • Hash Cond: (romeo2.tango_victor = romeo3.tango_victor)
2.          

CTE four

3. 138.266 3,441.932 ↓ 1.0 212,422 1

GroupAggregate (cost=225,605.620..230,881.570 rows=211,038 width=48) (actual time=2,954.576..3,441.932 rows=212,422 loops=1)

  • Group Key: (""zulu""((two.six)::text, 1, 11))
4. 1,665.374 3,303.666 ↓ 1.0 214,968 1

Sort (cost=225,605.620..226,133.210 rows=211,038 width=40) (actual time=2,954.565..3,303.666 rows=214,968 loops=1)

  • Sort Key: (""zulu""((two.six)::text, 1, 11))
  • Sort Method: external merge Disk: 7,160kB
5. 1,638.292 1,638.292 ↓ 1.0 214,968 1

Seq Scan on two (cost=0.000..201,170.820 rows=211,038 width=40) (actual time=0.014..1,638.292 rows=214,968 loops=1)

  • Filter: (seven >= 'foxtrot_charlie'::timestamp without time zone)
  • Rows Removed by Filter: 437,348
6.          

CTE foxtrot_juliet

7. 692.942 11,402.952 ↑ 358.9 212,422 1

Merge Join (cost=306,747.770..15,750,134.870 rows=76,247,795 width=73) (actual time=8,600.460..11,402.952 rows=212,422 loops=1)

  • Merge Cond: (romeo1.tango_victor = (""zulu""((quebec.six)::text, 1, 11)))
  • Join Filter: ((quebec.seven < romeo1.echo) AND (quebec.seven >= romeo1.yankee))
  • Rows Removed by Join Filter: 6,282
8. 317.911 3,903.288 ↓ 1.0 212,422 1

Sort (cost=29,376.560..29,904.150 rows=211,038 width=48) (actual time=3,749.575..3,903.288 rows=212,422 loops=1)

  • Sort Key: romeo1.tango_victor
  • Sort Method: external merge Disk: 8,744kB
9. 3,585.377 3,585.377 ↓ 1.0 212,422 1

CTE Scan on four (cost=0.000..4,220.760 rows=211,038 width=48) (actual time=2,954.578..3,585.377 rows=212,422 loops=1)

10. 308.392 6,806.722 ↓ 1.0 652,316 1

Materialize (cost=277,371.210..280,622.900 rows=650,338 width=25) (actual time=4,850.824..6,806.722 rows=652,316 loops=1)

11. 5,439.276 6,498.330 ↓ 1.0 652,316 1

Sort (cost=277,371.210..278,997.060 rows=650,338 width=25) (actual time=4,850.820..6,498.330 rows=652,316 loops=1)

  • Sort Key: (""zulu""((quebec.six)::text, 1, 11))
  • Sort Method: external merge Disk: 34,520kB
12. 1,059.054 1,059.054 ↓ 1.0 652,316 1

Seq Scan on two quebec (cost=0.000..199,017.380 rows=650,338 width=25) (actual time=0.014..1,059.054 rows=652,316 loops=1)

13.          

CTE india

14. 318.677 3,307.228 ↓ 1,062.1 212,422 1

HashAggregate (cost=1,906,194.880..1,906,196.880 rows=200 width=40) (actual time=3,145.448..3,307.228 rows=212,422 loops=1)

  • Group Key: romeo2kilo1.tango_victor
15. 2,988.551 2,988.551 ↑ 358.9 212,422 1

CTE Scan on foxtrot_juliet uniform (cost=0.000..1,524,955.900 rows=76,247,795 width=32) (actual time=0.001..2,988.551 rows=212,422 loops=1)

16. 8,667.644 8,667.644 ↑ 358.9 212,422 1

CTE Scan on foxtrot_juliet (cost=0.000..1,524,955.900 rows=76,247,795 width=92) (actual time=8,600.463..8,667.644 rows=212,422 loops=1)

17. 89.582 3,547.476 ↓ 212,422.0 212,422 1

Hash (cost=4.500..4.500 rows=1 width=40) (actual time=3,547.475..3,547.476 rows=212,422 loops=1)

  • Buckets: 65,536 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3,585kB
18. 3,457.894 3,457.894 ↓ 212,422.0 212,422 1

CTE Scan on india (cost=0.000..4.500 rows=1 width=40) (actual time=3,145.452..3,457.894 rows=212,422 loops=1)

  • Filter: (tango_romeo = 1)
Planning time : 0.297 ms
Execution time : 12,491.356 ms