explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZTOc

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 15,261.960 ↑ 1.0 50 1

Limit (cost=1,483,301.76..1,483,303.51 rows=50 width=826) (actual time=15,261.732..15,261.960 rows=50 loops=1)

2. 0.116 15,261.923 ↑ 15,292.0 50 1

WindowAgg (cost=1,483,301.76..1,502,416.71 rows=764,598 width=826) (actual time=15,261.730..15,261.923 rows=50 loops=1)

3.          

CTE raw

4. 1,219.187 1,219.187 ↓ 1.0 770,540 1

Seq Scan on question_answer_facts (cost=0.00..118,981.76 rows=762,665 width=337) (actual time=0.012..1,219.187 rows=770,540 loops=1)

  • Filter: (((question_id)::text <> ALL ('{bX3VUcvtQIWKIKA24oC4,5E11tPG0xiwOyOwoq4yyWA}'::text[])) AND (ts_question >= (now() - '45 days'::interval)))
  • Rows Removed by Filter: 964675
5. 3,947.362 15,261.807 ↑ 15,292.0 50 1

Sort (cost=1,364,320.00..1,366,231.49 rows=764,598 width=829) (actual time=15,261.715..15,261.807 rows=50 loops=1)

  • Sort Key: qaf.transaction_id, qaf.ts_question
  • Sort Method: external merge Disk: 242912kB
6. 3,970.948 11,314.445 ↓ 1.0 770,540 1

Hash Left Join (cost=68,792.66..730,337.18 rows=764,598 width=829) (actual time=561.003..11,314.445 rows=770,540 loops=1)

  • Hash Cond: ((qaf.session_id)::text = s.id)
7. 4,190.255 7,017.252 ↓ 1.0 770,540 1

Hash Left Join (cost=23,184.29..419,186.58 rows=764,598 width=1,316) (actual time=233.439..7,017.252 rows=770,540 loops=1)

  • Hash Cond: ((qaf.master_id)::text = master.id)
8. 577.814 2,596.136 ↓ 1.0 770,540 1

Hash Left Join (cost=8.84..20,333.87 rows=762,665 width=1,820) (actual time=0.141..2,596.136 rows=770,540 loops=1)

  • Hash Cond: ((qaf.conversation_id)::text = c.conversation_id)
9. 2,018.202 2,018.202 ↓ 1.0 770,540 1

CTE Scan on raw qaf (cost=0.00..15,253.30 rows=762,665 width=2,304) (actual time=0.014..2,018.202 rows=770,540 loops=1)

10. 0.024 0.120 ↑ 1.5 38 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
11. 0.011 0.096 ↑ 1.5 38 1

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

12. 0.057 0.085 ↑ 1.5 38 1

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

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

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

14. 129.258 230.861 ↑ 1.0 303,063 1

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

  • Buckets: 65536 Batches: 8 Memory Usage: 3531kB
15. 101.603 101.603 ↑ 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.033..101.603 rows=303,063 loops=1)

16. 170.755 326.245 ↑ 1.0 353,661 1

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

  • Buckets: 65536 Batches: 16 Memory Usage: 2380kB
17. 155.490 155.490 ↑ 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.009..155.490 rows=353,661 loops=1)

Planning time : 0.507 ms
Execution time : 15,342.181 ms