explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wyCp

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 15,293.930 ↑ 1.0 50 1

Limit (cost=1,482,888.34..1,482,890.09 rows=50 width=826) (actual time=15,293.747..15,293.930 rows=50 loops=1)

2. 0.085 15,293.904 ↑ 15,292.9 50 1

WindowAgg (cost=1,482,888.34..1,502,004.46 rows=764,645 width=826) (actual time=15,293.746..15,293.904 rows=50 loops=1)

3.          

CTE raw

4. 1,211.722 1,211.722 ↓ 1.0 770,646 1

Seq Scan on question_answer_facts (cost=0.00..118,981.76 rows=762,711 width=337) (actual time=0.013..1,211.722 rows=770,646 loops=1)

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

Sort (cost=1,363,906.58..1,365,818.19 rows=764,645 width=829) (actual time=15,293.731..15,293.819 rows=50 loops=1)

  • Sort Key: qaf.transaction_id, qaf.ts_question
  • Sort Method: external merge Disk: 243728kB
6. 3,436.703 11,229.297 ↓ 1.0 773,180 1

Hash Left Join (cost=68,422.70..729,883.82 rows=764,645 width=829) (actual time=575.840..11,229.297 rows=773,180 loops=1)

  • Hash Cond: ((qaf.session_id)::text = s.id)
7. 4,656.526 7,457.869 ↓ 1.0 773,180 1

Hash Left Join (cost=23,076.70..419,068.95 rows=764,645 width=1,316) (actual time=239.792..7,457.869 rows=773,180 loops=1)

  • Hash Cond: ((qaf.master_id)::text = master.id)
8. 565.490 2,563.822 ↓ 1.0 770,646 1

Hash Left Join (cost=8.84..20,335.09 rows=762,711 width=1,820) (actual time=0.143..2,563.822 rows=770,646 loops=1)

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

CTE Scan on raw qaf (cost=0.00..15,254.22 rows=762,711 width=2,304) (actual time=0.015..1,998.210 rows=770,646 loops=1)

10. 0.018 0.122 ↑ 1.5 38 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
11. 0.014 0.104 ↑ 1.5 38 1

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

12. 0.060 0.090 ↑ 1.5 38 1

HashAggregate (cost=6.96..7.54 rows=58 width=54) (actual time=0.076..0.090 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. 133.889 237.521 ↓ 1.0 303,442 1

Hash (cost=16,385.27..16,385.27 rows=300,127 width=49) (actual time=237.521..237.521 rows=303,442 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 3534kB
15. 103.632 103.632 ↓ 1.0 303,442 1

Seq Scan on dps_master master (cost=0.00..16,385.27 rows=300,127 width=49) (actual time=0.005..103.632 rows=303,442 loops=1)

16. 175.599 334.725 ↓ 1.0 353,661 1

Hash (cost=36,966.00..36,966.00 rows=346,000 width=66) (actual time=334.724..334.725 rows=353,661 loops=1)

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

Seq Scan on dps_session s (cost=0.00..36,966.00 rows=346,000 width=66) (actual time=0.008..159.126 rows=353,661 loops=1)

Planning time : 0.380 ms
Execution time : 15,373.265 ms