explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CPIJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 10,837.611 ↑ 1.0 49 1

Limit (cost=182,778.03..193,775.15 rows=50 width=8) (actual time=10,622.138..10,837.611 rows=49 loops=1)

2. 67.655 10,865.366 ↑ 13.1 49 1

Gather Merge (cost=182,778.03..324,420.91 rows=644 width=8) (actual time=10,622.136..10,865.366 rows=49 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 18.155 10,797.711 ↑ 16.8 16 3 / 3

Nested Loop Left Join (cost=181,778.01..323,346.56 rows=268 width=8) (actual time=10,609.607..10,797.711 rows=16 loops=3)

  • Filter: ((items."values" @> '{"2200000087252561": [1917125]}'::jsonb) OR (items."values" @> '{"2200000096644720": [1917125]}'::jsonb) OR (items."values" @> '{"2200000087252560": [1917125]}'::jsonb) OR (a_2200000087252528."values" @> '{"15902006": [1917125]}'::jsonb) OR (a_2200000096644715."values" @> '{"15902006": [1917125]}'::jsonb))
  • Rows Removed by Filter: 3,446
4. 3.259 10,772.631 ↑ 15.5 3,462 3 / 3

Nested Loop Left Join (cost=181,777.72..305,417.06 rows=53,800 width=1,413) (actual time=10,599.455..10,772.631 rows=3,462 loops=3)

5. 178.693 10,769.372 ↑ 15.5 3,462 3 / 3

Parallel Index Scan Backward using items_64_pkey on items_64 items (cost=181,777.44..288,696.47 rows=53,800 width=986) (actual time=10,599.435..10,769.372 rows=3,462 loops=3)

  • Filter: ((deleted_on IS NULL) AND (table_id = '2100000009109664'::bigint) AND ((hashed SubPlan 1) OR (hashed SubPlan 2) OR (hashed SubPlan 3)))
  • Rows Removed by Filter: 48,484
6.          

SubPlan (for Parallel Index Scan Backward)

7. 10,064.835 10,094.210 ↓ 3.8 10,387 3 / 3

Bitmap Heap Scan on item_texts_64 item_texts (cost=4,562.09..60,748.58 rows=2,730 width=8) (actual time=42.995..10,094.210 rows=10,387 loops=3)

  • Recheck Cond: ((table_id = '2100000009109664'::bigint) AND (field_id = '2200000087252529'::bigint))
  • Filter: (value ~~* '%09%'::text)
  • Rows Removed by Filter: 137,546
  • Heap Blocks: exact=52,084
8. 29.375 29.375 ↑ 1.0 148,071 3 / 3

Bitmap Index Scan on item_texts_64_unique_idx (cost=0.00..4,561.41 rows=152,085 width=0) (actual time=29.375..29.375 rows=148,071 loops=3)

  • Index Cond: ((table_id = '2100000009109664'::bigint) AND (field_id = '2200000087252529'::bigint))
9. 199.916 226.803 ↓ 0.0 0 3 / 3

Bitmap Heap Scan on item_texts_64 item_texts_1 (cost=4,207.46..60,186.43 rows=2,517 width=8) (actual time=226.803..226.803 rows=0 loops=3)

  • Recheck Cond: ((table_id = '2100000009109664'::bigint) AND (field_id = '2200000087252530'::bigint))
  • Filter: (value ~~* '%09%'::text)
  • Rows Removed by Filter: 147,933
  • Heap Blocks: exact=52,108
10. 26.887 26.887 ↓ 1.1 148,072 3 / 3

Bitmap Index Scan on item_texts_64_unique_idx (cost=0.00..4,206.83 rows=140,227 width=0) (actual time=26.887..26.887 rows=148,072 loops=3)

  • Index Cond: ((table_id = '2100000009109664'::bigint) AND (field_id = '2200000087252530'::bigint))
11. 231.462 269.666 ↓ 0.0 0 3 / 3

Bitmap Heap Scan on item_texts_64 item_texts_2 (cost=4,609.16..60,822.00 rows=2,757 width=8) (actual time=269.666..269.666 rows=0 loops=3)

  • Recheck Cond: ((table_id = '2100000009109664'::bigint) AND (field_id = '2200000087252532'::bigint))
  • Filter: (value ~~* '%09%'::text)
  • Rows Removed by Filter: 147,933
  • Heap Blocks: exact=52,089
12. 38.204 38.204 ↑ 1.0 148,072 3 / 3

Bitmap Index Scan on item_texts_64_unique_idx (cost=0.00..4,608.47 rows=153,591 width=0) (actual time=38.203..38.204 rows=148,072 loops=3)

  • Index Cond: ((table_id = '2100000009109664'::bigint) AND (field_id = '2200000087252532'::bigint))
13. 0.000 0.000 ↓ 0.0 0 10,387 / 3

Index Scan using items_29_pkey on items_29 a_2200000096644715 (cost=0.29..0.31 rows=1 width=435) (actual time=0.000..0.000 rows=0 loops=10,387)

  • Index Cond: (((items."values" ->> 'relation_id3'::text))::bigint = item_id)
  • Filter: ((deleted_on IS NULL) AND (table_id = 1,824,429))
14. 6.925 6.925 ↑ 1.0 1 10,387 / 3

Index Scan using items_29_pkey on items_29 a_2200000087252528 (cost=0.29..0.31 rows=1 width=435) (actual time=0.002..0.002 rows=1 loops=10,387)

  • Index Cond: (((items."values" ->> 'relation_id1'::text))::bigint = item_id)
  • Filter: ((deleted_on IS NULL) AND (table_id = 1,824,429))
Planning time : 5.985 ms
Execution time : 10,866.741 ms