explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 93EL

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 9,068.554 ↑ 1.0 1 1

GroupAggregate (cost=22,965.62..22,985.77 rows=1 width=700) (actual time=9,068.554..9,068.554 rows=1 loops=1)

  • Group Key: f.id, (CASE f.sharing WHEN 'R'::bpchar THEN 'Reporting'::text WHEN 'P'::bpchar THEN 'Peer review'::text WHEN 'V'::bpchar THEN 'Verification'::text WHEN 'D'::bpchar THEN 'Data flow'::text WHEN 'M'::bpchar THEN 'Moderation'::text WHEN 'E'::bpchar THEN 'Editing'::text ELSE NULL::text END), (((f.definition)::json ->> 'searchArea'::text)), (st_astext(st_transform(st_geomfromtext(((f.definition)::json ->> 'searchArea'::text), 3857), 4326)))
2. 0.030 9,068.529 ↑ 1,090.0 1 1

Sort (cost=22,965.62..22,968.34 rows=1,090 width=3,614) (actual time=9,068.529..9,068.529 rows=1 loops=1)

  • Sort Key: (CASE f.sharing WHEN 'R'::bpchar THEN 'Reporting'::text WHEN 'P'::bpchar THEN 'Peer review'::text WHEN 'V'::bpchar THEN 'Verification'::text WHEN 'D'::bpchar THEN 'Data flow'::text WHEN 'M'::bpchar THEN 'Moderation'::text WHEN 'E'::bpchar THEN 'Editing'::text ELSE NULL::text END), (((f.definition)::json ->> 'searchArea'::text)), (st_astext(st_transform(st_geomfromtext(((f.definition)::json ->> 'searchArea'::text), 3857), 4326)))
  • Sort Method: quicksort Memory: 27kB
3. 8,782.960 9,068.499 ↑ 1,090.0 1 1

Nested Loop Left Join (cost=0.29..22,910.63 rows=1,090 width=3,614) (actual time=9,068.494..9,068.499 rows=1 loops=1)

  • Join Filter: (string_to_array(COALESCE(((f.definition)::json ->> 'location_list'::text), ((f.definition)::json ->> 'location_ids'::text)), ','::text) @> ARRAY[(l.id)::text])
  • Rows Removed by Join Filter: 211245
4. 0.056 0.056 ↑ 1.0 1 1

Index Scan using pk_filters on filters f (cost=0.29..8.30 rows=1 width=574) (actual time=0.053..0.056 rows=1 loops=1)

  • Index Cond: (id = 11188)
  • Filter: ((NOT deleted) AND ((website_id = 23) OR (website_id IS NULL)))
5. 285.483 285.483 ↑ 1.0 211,245 1

Seq Scan on locations l (cost=0.00..13,187.76 rows=218,076 width=2,950) (actual time=0.008..285.483 rows=211,245 loops=1)

Planning time : 1.063 ms