explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bO1

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 13,980.458 ↑ 1.0 50 1

Limit (cost=1,482,342.48..1,482,344.23 rows=50 width=826) (actual time=13,980.258..13,980.458 rows=50 loops=1)

2. 0.083 13,980.426 ↑ 15,280.9 50 1

WindowAgg (cost=1,482,342.48..1,501,443.63 rows=764,046 width=826) (actual time=13,980.256..13,980.426 rows=50 loops=1)

3.          

CTE raw

4. 1,209.771 1,209.771 ↓ 1.0 770,516 1

Seq Scan on question_answer_facts (cost=0.00..118,957.30 rows=762,115 width=337) (actual time=0.012..1,209.771 rows=770,516 loops=1)

  • Filter: (((question_id)::text <> ALL ('{bX3VUcvtQIWKIKA24oC4,5E11tPG0xiwOyOwoq4yyWA}'::text[])) AND (ts_question >= (now() - '45 days'::interval)))
  • Rows Removed by Filter: 964699
5. 4,113.103 13,980.343 ↑ 15,280.9 50 1

Sort (cost=1,363,385.18..1,365,295.29 rows=764,046 width=829) (actual time=13,980.243..13,980.343 rows=50 loops=1)

  • Sort Key: qaf.transaction_id, qaf.ts_question
  • Sort Method: external merge Disk: 242912kB
6. 5,322.902 9,867.240 ↓ 1.0 770,516 1

Hash Left Join (cost=68,792.66..729,866.28 rows=764,046 width=829) (actual time=594.102..9,867.240 rows=770,516 loops=1)

  • Hash Cond: ((qaf.session_id)::text = s.id)
7. 1,387.938 4,201.804 ↓ 1.0 770,516 1

Hash Left Join (cost=23,184.29..418,903.27 rows=764,046 width=1,316) (actual time=250.682..4,201.804 rows=770,516 loops=1)

  • Hash Cond: ((qaf.master_id)::text = master.id)
8. 565.160 2,565.272 ↓ 1.0 770,516 1

Hash Left Join (cost=8.84..20,319.21 rows=762,115 width=1,820) (actual time=0.140..2,565.272 rows=770,516 loops=1)

  • Hash Cond: ((qaf.conversation_id)::text = c.conversation_id)
9. 1,999.993 1,999.993 ↓ 1.0 770,516 1

CTE Scan on raw qaf (cost=0.00..15,242.30 rows=762,115 width=2,304) (actual time=0.015..1,999.993 rows=770,516 loops=1)

10. 0.017 0.119 ↑ 1.5 38 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
11. 0.016 0.102 ↑ 1.5 38 1

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

12. 0.056 0.086 ↑ 1.5 38 1

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

  • Group Key: travelbot_conversations.conversation_id, travelbot_conversations.conversation_title
13. 0.030 0.030 ↓ 1.1 70 1

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

14. 139.540 248.594 ↑ 1.0 303,063 1

Hash (cost=16,418.42..16,418.42 rows=303,442 width=49) (actual time=248.594..248.594 rows=303,063 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3531kB
15. 109.054 109.054 ↑ 1.0 303,063 1

Seq Scan on dps_master master (cost=0.00..16,418.42 rows=303,442 width=49) (actual time=0.036..109.054 rows=303,063 loops=1)

16. 178.358 342.534 ↑ 1.0 353,661 1

Hash (cost=37,042.61..37,042.61 rows=353,661 width=66) (actual time=342.534..342.534 rows=353,661 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2380kB
17. 164.176 164.176 ↑ 1.0 353,661 1

Seq Scan on dps_session s (cost=0.00..37,042.61 rows=353,661 width=66) (actual time=0.006..164.176 rows=353,661 loops=1)

Planning time : 0.552 ms
Execution time : 14,060.963 ms