explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yDgI : Optimization for: plan #lErS

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2,264.086 60,343.448 ↑ 1.0 1 1

Aggregate (cost=11,773,787.84..11,773,787.85 rows=1 width=48) (actual time=60,343.448..60,343.448 rows=1 loops=1)

2. 1,518.604 58,079.362 ↑ 6.7 4,394,014 1

Hash Join (cost=6,495,034.76..10,604,255.12 rows=29,238,318 width=34) (actual time=38,284.931..58,079.362 rows=4,394,014 loops=1)

  • Hash Cond: (og_q.og_question_object_id = og_o.id)
3. 2,452.603 55,395.533 ↑ 4.8 6,048,160 1

Merge Join (cost=6,261,206.15..10,208,209.07 rows=29,238,318 width=16) (actual time=29,103.589..55,395.533 rows=6,048,160 loops=1)

  • Merge Cond: (og_q.document_id = dn.document_id)
4. 23,029.187 23,029.187 ↑ 1.4 30,528,662 1

Index Scan using fk_idx_bhpx on og_question og_q (cost=0.56..3,415,405.07 rows=42,995,336 width=16) (actual time=0.021..23,029.187 rows=30,528,662 loops=1)

5. 2,034.056 29,913.743 ↑ 1.1 6,048,272 1

Sort (cost=6,252,569.62..6,268,922.80 rows=6,541,272 width=16) (actual time=29,097.686..29,913.743 rows=6,048,272 loops=1)

  • Sort Key: dn.document_id
  • Sort Method: quicksort Memory: 422,466kB
6. 18,300.242 27,879.687 ↑ 1.2 5,284,320 1

Bitmap Heap Scan on document_n dn (cost=962,479.47..5,512,060.35 rows=6,541,272 width=16) (actual time=11,022.739..27,879.687 rows=5,284,320 loops=1)

  • Recheck Cond: (category = 2)
  • Filter: ((d_deleted = 0) AND ((rdate)::date >= '2018-07-02'::date))
  • Rows Removed by Filter: 22,779,336
  • Heap Blocks: exact=3,889,295
7. 9,579.445 9,579.445 ↓ 1.0 28,425,783 1

Bitmap Index Scan on dn_ctg_orgid_n_docid_idx (cost=0.00..960,844.15 rows=28,091,144 width=0) (actual time=9,579.445..9,579.445 rows=28,425,783 loops=1)

  • Index Cond: (category = 2)
8. 174.637 1,165.225 ↑ 1.0 919,612 1

Hash (cost=222,223.70..222,223.70 rows=928,392 width=26) (actual time=1,165.225..1,165.225 rows=919,612 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 51,848kB
9. 207.302 990.588 ↑ 1.0 919,612 1

Hash Left Join (cost=110,882.46..222,223.70 rows=928,392 width=26) (actual time=407.457..990.588 rows=919,612 loops=1)

  • Hash Cond: (og_o.id = og_o_1.id)
10. 377.492 377.492 ↑ 1.0 919,612 1

Seq Scan on og_question_object og_o (cost=0.00..105,052.92 rows=928,392 width=10) (actual time=0.024..377.492 rows=919,612 loops=1)

11. 46.804 405.794 ↑ 1.0 276,334 1

Hash (cost=107,373.90..107,373.90 rows=280,685 width=16) (actual time=405.794..405.794 rows=276,334 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 17,050kB
12. 358.990 358.990 ↑ 1.0 276,334 1

Seq Scan on og_question_object og_o_1 (cost=0.00..107,373.90 rows=280,685 width=16) (actual time=0.004..358.990 rows=276,334 loops=1)

  • Filter: (cdate >= '2018-07-02 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 643,278
Planning time : 4.897 ms
Execution time : 60,366.523 ms