explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9aI4

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 610.605 ↑ 1.0 100 1

Limit (cost=0.86..1,189.18 rows=100 width=174) (actual time=607.492..610.605 rows=100 loops=1)

2. 321.834 610.595 ↑ 31,693.4 100 1

Nested Loop Left Join (cost=0.86..37,661,862.43 rows=3,169,343 width=174) (actual time=607.491..610.595 rows=100 loops=1)

  • Filter: (((orthographies.locale = ANY ('{en-US,en,und}'::text[])) AND (NOT (SubPlan 1))) OR ((orthographies.locale = ANY ('{en-US,en,und}'::text[])) AND (NOT (SubPlan 2))) OR (alternatives: SubPlan 3 or hashed SubPlan 4))
  • Rows Removed by Filter: 1,193
3. 0.257 0.257 ↑ 18,095.1 304 1

Index Scan Backward using descending_written_form on orthographies (cost=0.43..171,555.27 rows=5,500,914 width=56) (actual time=0.014..0.257 rows=304 loops=1)

4. 1.824 1.824 ↓ 4.0 4 304

Index Scan using ix_tokens_orthography_id on tokens (cost=0.43..0.47 rows=1 width=118) (actual time=0.005..0.006 rows=4 loops=304)

  • Index Cond: (orthographies.id = orthography_id)
5.          

SubPlan (for Nested Loop Left Join)

6. 0.200 0.200 ↑ 1.0 1 100

Index Only Scan using ix_tokens_orthography_id on tokens tokens_1 (cost=0.43..1.55 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (orthography_id = orthographies.id)
  • Heap Fetches: 0
7. 0.500 0.500 ↑ 2.0 1 100

Index Only Scan using ix_pronunciations_token_id on pronunciations (cost=0.43..1.57 rows=2 width=0) (actual time=0.005..0.005 rows=1 loops=100)

  • Index Cond: (token_id = tokens.id)
  • Heap Fetches: 0
8. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_pronunciations_token_id on pronunciations pronunciations_1 (cost=0.43..3.78 rows=1 width=0) (never executed)

  • Index Cond: (tokens.id = token_id)
  • Filter: (lex_locale = ANY ('{en-US,en,und}'::text[]))
9. 204.640 285.980 ↓ 1.0 1,200,525 1

Bitmap Heap Scan on pronunciations pronunciations_2 (cost=12,684.90..200,175.28 rows=1,192,901 width=16) (actual time=84.772..285.980 rows=1,200,525 loops=1)

  • Recheck Cond: (lex_locale = ANY ('{en-US,en,und}'::text[]))
  • Heap Blocks: exact=27,585
10. 81.340 81.340 ↓ 1.0 1,200,525 1

Bitmap Index Scan on ix_pronunciations_lex_locale (cost=0.00..12,386.67 rows=1,192,901 width=0) (actual time=81.340..81.340 rows=1,200,525 loops=1)

  • Index Cond: (lex_locale = ANY ('{en-US,en,und}'::text[]))
Planning time : 1.010 ms
Execution time : 613.260 ms