explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QjGZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 2,193.585 ↑ 1.0 10 1

Limit (cost=196,393.20..196,393.20 rows=10 width=162) (actual time=2,193.582..2,193.585 rows=10 loops=1)

2. 0.023 2,193.581 ↑ 1.0 10 1

Sort (cost=196,393.20..196,393.20 rows=10 width=162) (actual time=2,193.581..2,193.581 rows=10 loops=1)

  • Sort Key: (COALESCE(((COALESCE(entities.s_relatedness, '0'::double precision) + (CASE WHEN ((i_entities.name)::text ~~* 'M'::text) THEN length((i_entities.name)::text) ELSE 0 END)::double precision)), b.score)) DESC
  • Sort Method: quicksort Memory: 27kB
3. 0.028 2,193.558 ↓ 1.1 11 1

Hash Full Join (cost=196,393.10..196,393.17 rows=10 width=162) (actual time=2,193.542..2,193.558 rows=11 loops=1)

  • Hash Cond: ((i_entities.crunchbase_id)::text = (b.crunchbase_id)::text)
4. 0.004 2,177.989 ↑ 1.0 10 1

Limit (cost=189,982.25..189,982.26 rows=10 width=63) (actual time=2,177.984..2,177.989 rows=10 loops=1)

5. 69.614 2,177.985 ↑ 25,837.9 10 1

Sort (cost=189,982.25..190,111.44 rows=258,379 width=63) (actual time=2,177.983..2,177.985 rows=10 loops=1)

  • Sort Key: ((COALESCE(entities.s_relatedness, '0'::double precision) + (CASE WHEN ((i_entities.name)::text ~~* 'M'::text) THEN length((i_entities.name)::text) ELSE 0 END)::double precision)) DESC
  • Sort Method: top-N heapsort Memory: 26kB
6. 464.044 2,108.371 ↓ 1.0 263,873 1

Hash Right Join (cost=168,344.10..188,865.56 rows=258,379 width=63) (actual time=1,406.953..2,108.371 rows=263,873 loops=1)

  • Hash Cond: ((entities.crunchbase_id)::text = (i_entities.crunchbase_id)::text)
7. 237.710 237.710 ↑ 1.0 1,176,936 1

Seq Scan on entities (cost=0.00..19,386.81 rows=1,176,936 width=43) (actual time=0.017..237.710 rows=1,176,936 loops=1)

8. 112.525 1,406.617 ↓ 1.0 263,873 1

Hash (cost=167,439.77..167,439.77 rows=258,379 width=36) (actual time=1,406.616..1,406.617 rows=263,873 loops=1)

  • Buckets: 524288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 22681kB
9. 1,209.609 1,294.092 ↓ 1.0 263,873 1

Bitmap Heap Scan on i_entities (cost=1,482.49..167,439.77 rows=258,379 width=36) (actual time=115.322..1,294.092 rows=263,873 loops=1)

  • Recheck Cond: ((' '::text || (name)::text) ~~* '% m%'::text)
  • Rows Removed by Index Recheck: 3469
  • Heap Blocks: exact=130477
10. 84.483 84.483 ↓ 1.1 277,843 1

Bitmap Index Scan on i_entities_name_trigrams (cost=0.00..1,469.57 rows=258,379 width=0) (actual time=84.482..84.483 rows=277,843 loops=1)

  • Index Cond: ((' '::text || (name)::text) ~~* '% m%'::text)
11. 0.006 15.541 ↑ 1.0 10 1

Hash (cost=6,410.81..6,410.81 rows=10 width=90) (actual time=15.541..15.541 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.004 15.535 ↑ 1.0 10 1

Subquery Scan on b (cost=6,410.78..6,410.81 rows=10 width=90) (actual time=15.530..15.535 rows=10 loops=1)

13. 0.002 15.531 ↑ 1.0 10 1

Limit (cost=6,410.78..6,410.78 rows=10 width=90) (actual time=15.529..15.531 rows=10 loops=1)

14. 1.570 15.529 ↑ 495.1 10 1

Sort (cost=6,410.78..6,413.25 rows=4,951 width=90) (actual time=15.528..15.529 rows=10 loops=1)

  • Sort Key: ((COALESCE(entities_1.s_relatedness, '0'::double precision) + (CASE WHEN ((entities_1.name)::text ~~* 'M'::text) THEN length((entities_1.name)::text) ELSE 0 END)::double precision)) DESC
  • Sort Method: top-N heapsort Memory: 26kB
15. 13.081 13.959 ↑ 1.0 4,935 1

Bitmap Heap Scan on entities entities_1 (cost=19.67..6,389.38 rows=4,951 width=90) (actual time=1.278..13.959 rows=4,935 loops=1)

  • Recheck Cond: ((' '::text || (name)::text) ~~* '% m%'::text)
  • Rows Removed by Index Recheck: 58
  • Heap Blocks: exact=3116
16. 0.878 0.878 ↓ 1.1 5,391 1

Bitmap Index Scan on entities_name_trigrams (cost=0.00..19.43 rows=4,951 width=0) (actual time=0.878..0.878 rows=5,391 loops=1)

  • Index Cond: ((' '::text || (name)::text) ~~* '% m%'::text)
Planning time : 18.513 ms
Execution time : 2,195.405 ms