explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sjWL

Settings
# exclusive inclusive rows x rows loops node
1. 3,134.345 70,737.157 ↓ 1.0 770,710 1

WindowAgg (cost=1,479,449.29..1,498,506.44 rows=762,286 width=826) (actual time=62,638.789..70,737.157 rows=770,710 loops=1)

2.          

CTE raw

3. 17,231.672 17,231.672 ↓ 1.0 770,710 1

Seq Scan on question_answer_facts (cost=0.00..119,043.66 rows=760,359 width=337) (actual time=11.501..17,231.672 rows=770,710 loops=1)

  • Filter: (((question_id)::text <> ALL ('{bX3VUcvtQIWKIKA24oC4,5E11tPG0xiwOyOwoq4yyWA}'::text[])) AND (ts_question >= (now() - '45 days'::interval)))
  • Rows Removed by Filter: 965811
4. 15,604.995 67,602.812 ↓ 1.0 770,710 1

Sort (cost=1,360,405.63..1,362,311.35 rows=762,286 width=829) (actual time=62,638.771..67,602.812 rows=770,710 loops=1)

  • Sort Key: qaf.transaction_id, qaf.ts_question
  • Sort Method: external merge Disk: 242992kB
5. 4,929.858 51,997.817 ↓ 1.0 770,710 1

Hash Left Join (cost=68,792.66..728,359.39 rows=762,286 width=829) (actual time=16,615.537..51,997.817 rows=770,710 loops=1)

  • Hash Cond: ((qaf.session_id)::text = s.id)
6. 5,981.508 37,899.146 ↓ 1.0 770,710 1

Hash Left Join (cost=23,184.29..417,998.59 rows=762,286 width=1,316) (actual time=7,439.674..37,899.146 rows=770,710 loops=1)

  • Hash Cond: ((qaf.master_id)::text = master.id)
7. 2,839.645 24,517.750 ↓ 1.0 770,710 1

Hash Left Join (cost=8.84..20,272.41 rows=760,359 width=1,820) (actual time=23.676..24,517.750 rows=770,710 loops=1)

  • Hash Cond: ((qaf.conversation_id)::text = c.conversation_id)
8. 21,665.947 21,665.947 ↓ 1.0 770,710 1

CTE Scan on raw qaf (cost=0.00..15,207.18 rows=760,359 width=2,304) (actual time=11.504..21,665.947 rows=770,710 loops=1)

9. 0.035 12.158 ↑ 1.5 38 1

Hash (cost=8.12..8.12 rows=58 width=54) (actual time=12.157..12.158 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
10. 0.026 12.123 ↑ 1.5 38 1

Subquery Scan on c (cost=6.96..8.12 rows=58 width=54) (actual time=12.072..12.123 rows=38 loops=1)

11. 0.105 12.097 ↑ 1.5 38 1

HashAggregate (cost=6.96..7.54 rows=58 width=54) (actual time=12.070..12.097 rows=38 loops=1)

  • Group Key: travelbot_conversations.conversation_id, travelbot_conversations.conversation_title
12. 11.992 11.992 ↓ 1.1 70 1

Seq Scan on travelbot_conversations (cost=0.00..6.64 rows=64 width=54) (actual time=7.970..11.992 rows=70 loops=1)

13. 1,628.097 7,399.888 ↑ 1.0 303,429 1

Hash (cost=16,418.42..16,418.42 rows=303,442 width=49) (actual time=7,399.888..7,399.888 rows=303,429 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3534kB
14. 5,771.791 5,771.791 ↑ 1.0 303,429 1

Seq Scan on dps_master master (cost=0.00..16,418.42 rows=303,442 width=49) (actual time=11.653..5,771.791 rows=303,429 loops=1)

15. 1,337.048 9,168.813 ↓ 1.0 354,079 1

Hash (cost=37,042.61..37,042.61 rows=353,661 width=66) (actual time=9,168.812..9,168.813 rows=354,079 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2383kB
16. 7,831.765 7,831.765 ↓ 1.0 354,079 1

Seq Scan on dps_session s (cost=0.00..37,042.61 rows=353,661 width=66) (actual time=15.525..7,831.765 rows=354,079 loops=1)

Planning time : 143.054 ms