explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YylQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 2,055.245 ↑ 1.0 10 1

Limit (cost=196,397.20..196,397.20 rows=10 width=162) (actual time=2,055.241..2,055.245 rows=10 loops=1)

2. 0.018 2,055.240 ↑ 1.0 10 1

Sort (cost=196,397.20..196,397.20 rows=10 width=162) (actual time=2,055.240..2,055.240 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.023 2,055.222 ↓ 1.1 11 1

Hash Full Join (cost=196,397.10..196,397.17 rows=10 width=162) (actual time=2,055.206..2,055.222 rows=11 loops=1)

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

Limit (cost=189,986.25..189,986.26 rows=10 width=63) (actual time=2,039.592..2,039.596 rows=10 loops=1)

5. 68.006 2,039.591 ↑ 25,837.9 10 1

Sort (cost=189,986.25..190,115.44 rows=258,379 width=63) (actual time=2,039.590..2,039.591 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. 467.590 1,971.585 ↓ 1.0 263,879 1

Hash Right Join (cost=168,348.10..188,869.56 rows=258,379 width=63) (actual time=1,235.719..1,971.585 rows=263,879 loops=1)

  • Hash Cond: ((entities.crunchbase_id)::text = (i_entities.crunchbase_id)::text)
7. 268.635 268.635 ↑ 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.027..268.635 rows=1,176,936 loops=1)

8. 108.815 1,235.360 ↓ 1.0 263,879 1

Hash (cost=167,443.77..167,443.77 rows=258,379 width=36) (actual time=1,235.360..1,235.360 rows=263,879 loops=1)

  • Buckets: 524288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 22681kB
9. 1,071.339 1,126.545 ↓ 1.0 263,879 1

Bitmap Heap Scan on i_entities (cost=1,486.49..167,443.77 rows=258,379 width=36) (actual time=85.102..1,126.545 rows=263,879 loops=1)

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

Bitmap Index Scan on i_entities_name_trigrams (cost=0.00..1,473.57 rows=258,379 width=0) (actual time=55.206..55.206 rows=277,851 loops=1)

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

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

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

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

13. 0.003 15.591 ↑ 1.0 10 1

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

14. 1.528 15.588 ↑ 495.1 10 1

Sort (cost=6,410.78..6,413.25 rows=4,951 width=90) (actual time=15.587..15.588 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.169 14.060 ↑ 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.294..14.060 rows=4,935 loops=1)

  • Recheck Cond: ((' '::text || (name)::text) ~~* '% m%'::text)
  • Rows Removed by Index Recheck: 58
  • Heap Blocks: exact=3116
16. 0.891 0.891 ↓ 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.891..0.891 rows=5,391 loops=1)

  • Index Cond: ((' '::text || (name)::text) ~~* '% m%'::text)