explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lUGm

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,229.340 ↑ 1.0 4 1

Limit (cost=27,710.14..27,712.23 rows=4 width=853) (actual time=4,229.313..4,229.340 rows=4 loops=1)

2. 0.184 4,229.338 ↑ 1.0 4 1

GroupAggregate (cost=27,710.14..27,712.23 rows=4 width=853) (actual time=4,229.312..4,229.338 rows=4 loops=1)

  • Group Key: t0.id, shipper.id
3. 0.028 4,229.154 ↑ 1.0 4 1

Sort (cost=27,710.14..27,710.15 rows=4 width=801) (actual time=4,229.153..4,229.154 rows=4 loops=1)

  • Sort Key: t0.id DESC, shipper.id
  • Sort Method: quicksort Memory: 29kB
4. 0.452 4,229.126 ↑ 1.0 4 1

Nested Loop Left Join (cost=0.00..27,710.10 rows=4 width=801) (actual time=2,354.502..4,229.126 rows=4 loops=1)

  • Join Filter: (pps.id = proposal_publish.proposal_id)
  • Rows Removed by Join Filter: 3626
  • Filter: (((t0.content ->> 'object_type'::text) <> 'proposal'::text) OR (proposal_publish.proposal_id IS NOT NULL) OR ((t0.content ->> 'no_detail'::text) = 'true'::text))
5. 3,595.792 4,103.386 ↑ 1.0 4 1

Nested Loop Left Join (cost=0.00..2,698.74 rows=4 width=805) (actual time=2,229.272..4,103.386 rows=4 loops=1)

  • Join Filter: (((pps.active = 1) AND ((t0.content ->> 'object_type'::text) = 'proposal'::text) AND ((t0.content ->> 'proposal_ikkatsu_id'::text) IS NULL) AND (((t0.content ->> 'object_id'::text))::integer = pps.id)) OR (((t0.content ->> 'proposal_ikkatsu_id'::text) IS NOT NULL) AND (((t0.content ->> 'proposal_ikkatsu_id'::text))::integer = pps.ikkatsu_id)))
  • Rows Removed by Join Filter: 2455700
  • Filter: ((((t0.content ->> 'object_type'::text) <> 'proposal'::text) OR (pps.auction_date >= '2019-06-06'::date)) AND (((t0.content ->> 'object_type'::text) <> 'proposal'::text) OR (to_char((pps.auction_date)::timestamp with time zone, 'yyyy-mm-dd'::text) <= '2019-06-06'::text)))
  • Rows Removed by Filter: 972
6. 20.305 74.928 ↓ 774.0 774 1

Nested Loop Left Join (cost=0.00..2,251.31 rows=1 width=797) (actual time=0.368..74.928 rows=774 loops=1)

  • Join Filter: (((t0.content ->> 'shipper_id'::text))::integer = shipper.id)
  • Rows Removed by Join Filter: 21122
7. 49.205 49.205 ↓ 774.0 774 1

Seq Scan on timeline t0 (cost=0.00..2,245.75 rows=1 width=713) (actual time=0.329..49.205 rows=774 loops=1)

  • Filter: ((destination IS NOT NULL) AND ((content ->> 'object_id'::text) IS NOT NULL) AND ((content ->> 'object_type'::text) IS NOT NULL) AND (active = 1) AND (status = 1) AND (((content ->> 'object_type'::text) = 'proposal'::text) OR (created_at >= '2019-06-06 00:00:00'::timestamp without time zone)) AND (((content ->> 'object_type'::text) = 'proposal'::text) OR (to_char(created_at, 'yyyy-mm-dd'::text) <= '2019-06-06'::text)) AND (((content ->> 'object_type'::text) <> 'news'::text) OR (to_timestamp((content ->> 'publish_at'::text), 'YYYY/MM/DD HH24:MI:SS'::text) <= now())) AND timeline_match_val(destination, '{market,ids}'::text[], '12596'::text, 'contain'::text, 'no'::text) AND timeline_match_val(destination, '{market,is_admin}'::text[], 'true'::text, 'equal'::text, 'yes'::text) AND timeline_match_val(destination, '{market,region_ids}'::text[], '"014"'::text, 'contain'::text, 'yes'::text) AND timeline_match_val(destination, '{market,nojoin}'::text[], 'true'::text, 'equal'::text, 'yes'::text))
  • Rows Removed by Filter: 1107
8. 5.418 5.418 ↑ 1.9 28 774

Seq Scan on ninushi shipper (cost=0.00..4.52 rows=52 width=84) (actual time=0.002..0.007 rows=28 loops=774)

9. 432.666 432.666 ↓ 1.0 3,174 774

Seq Scan on proposal pps (cost=0.00..255.68 rows=3,068 width=14) (actual time=0.002..0.559 rows=3,174 loops=774)

10. 0.444 125.288 ↓ 1.1 907 4

Materialize (cost=0.00..24,927.70 rows=858 width=4) (actual time=0.654..31.322 rows=907 loops=4)

11. 0.123 124.844 ↓ 1.1 907 1

Append (cost=0.00..24,923.41 rows=858 width=4) (actual time=2.608..124.844 rows=907 loops=1)

12. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on proposal_publish (cost=0.00..0.00 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=1)

  • Filter: (customer_id = 12596)
13. 0.015 0.015 ↓ 0.0 0 1

Seq Scan on proposal_publish proposal_publish_1 (cost=0.00..1.41 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=1)

  • Filter: (customer_id = 12596)
  • Rows Removed by Filter: 38
14. 124.691 124.691 ↓ 1.1 907 1

Seq Scan on proposal_publish proposal_publish_2 (cost=0.00..24,922.00 rows=856 width=4) (actual time=2.577..124.691 rows=907 loops=1)

  • Filter: (customer_id = 12596)
  • Rows Removed by Filter: 1024923
Planning time : 1.650 ms
Execution time : 4,230.029 ms